너무 헷갈리는 mysql 문법 연습문제들

출처 : 코드스테이츠

 

 

 

 

 

Assume you have the following users table and data in a SQL database: Write a SQL query to select all columns for all users under 21.

 

 

SELECT * FROM users WHERE age < 21;

 

---

Assume you have the following users table and data in a SQL database: Write a query to insert a new user Dog. Dog is 64 and likes chasing cars.

 

INSERT INTO users(name, Hobby, Age) VALUES ('Dog', 'chasing cars', 64);

 

---

 

Assume you have a users table and a comments table. A user has many comments. What tables and/or columns are required to encode this relationship in a SQL database?

 

A

A user_id column in the comments table

B

A comment_ids column in the users table

C

A join table with columns user_id and comment_id

 

A

A user_id column in the comments table

 

---

 

Assume you have the following users and pets tables: Write a query to select all users (and all their columns) that own at least one pet.

 

SELECT * FROM USERS u INNER JOIN PETS p ON u.id = p.owner_id;

 

---

 

Assume you have the following users and pets tables: Write a query to select two columns: a pet's name, and its owner's name. Be sure to use all pets in the results, even if the pet has no owner.

 

SELECT p.name, u.name FROM USERS u RIGHT JOIN PETS p ON u.id = p.owner_id

 

---

 

Assume you have a users table and a foods table. A user can specify any number of foods as their favorite. What tables and/or columns are required to encode this relationship in a SQL database?

 

A

A user_id column in the foods table

B

A food_ids column in the users table

C

A join table with columns user_id and food_id

 

A join table with columns user_id and food_id

 

 

---

 

 

 

Assume you have the following users, foods, and favorite_foods tables: Write a query to select all users and their favorite foods. Be sure to use all users in the results, even if the user has no favorite food.

 

SELECT u.id, u.name, f.name FROM users u LEFT JOIN favorite_foods ff ON u.id = ff.user_id LEFT JOIN foods f ON ff.food_id = f.id;