Introduction SQL statements allow us to retrieve, manipulate, or delete data sto

Question

Introduction

SQL statements allow us to retrieve, manipulate, or delete data stored in relational databases. Here are a 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 uses:

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 function:

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

Write an SQL script to:

a) Insert two a new user;

b) Retrieve the list of places in a given category;

c) Find the number of ratings submitted by a given user;

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

Explanation / Answer

lets first start with creating a new table

CREATE TABLE user( user_id INT , name VARCHAR(20), place VARCHAR(20), Rating INT);

This will create a table with name as user and will contain 4 columns and name of each column is followed by their datatype.

our table will look like:

A) Here we have to insert two users in table,

INSERT INTO user( user_id , name , place , Rating ) VALUES (134, 'John', 'Chicago', 5),(342,'Clay','Newyork',3);

This will insert two users in table with values given in query.

B) here we have to retrieve values of column given column name.

here we will retrieve values from column place

SELECT place FROM user;

this will retrive column from table whose name is place. Result of the query will be following.

C) here our purpose is to use where condition to retrieve some value for a particular user.

let us take suppose here we have retrive rating where user_id is 134.

SELECT Rating FROM user WHERE user_id =134;

this query will give us rating given by user_id 134. following table will be the output.

D) in this part of question we have to delete all preferences submitted by a user with a given ID =342

DELETE FROM user WHERE user_id = 342;

this will delete the second row from our table and our table will look lik

Note :- here i have created new table since no table was given in question, you can apply same queries to your table if you have one with just slight variations if required.

feel free to ask doubts in comments. :)

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.