Introduction

SQL statements allow us to retrieve, manipulate, or delete data stored in relational databases. Here are few examples of SQL statements:

The INSERT statement is used to insert data into a table: For example:

INSERT INTO `students (student_id`, `first_name`, `age`) VALUES (14, Melissa, 23);

The SELECT statement is used to retrieve data. For example, to get the list of students use:

SELECT * FROM `students`;

Where * means to return a table with all columns of the initial table.

You can limit the output columns:

SELECT `first_name` FROM `students`;

To use a condition, you can add a WHERE statement

SELECT * FROM `students` WHERE student_id = 12;

If you need to aggregate information in the table, you can use a function like MAX, or AVG for average, or COUNT.

For example, if you need to calculate an average students age, you would use this fucntion:

SELECT AVG(`age`) FROM `students`;

The DELETE statement can be used to delete data. Note that if no WHERE conditions are given all data from the table will be deleted.

For, example, to delete all re-enrollment records for a student with id equal to 14:

DELETE from `enrollment` WHERE `student_id`=14

Directions

For the database design in the design assignment for this week write an SQL script to:

Insert two a new user;

Retrieve the list of places in a given category;

Find the number of ratings submitted by a given user;

Delete all preferences submitted by a user with a given ID = 342