1.Create a student table to store name, age and score of students.

And Add The Details into the

table.

details datatype

name string of max length 200

age integer

score integer

Ann-

CREATE TABLE student(name VARCHAR(200), age INT, score INT);

2. In a typical e-commerce application, we need to store the following customer details. Create a
customer

table to store the data.

In a typical e-commerce application, we need to store the following customer details. Create a

customer

table to store the data.

details datatype

customer_id integer

string of max length

first_name
200

string of max length

last_name
200

date_of_birth date

address text
CREATE TABLE customer(

customer_id INT,

first_name VARCHAR(200),

last_name VARCHAR(200),

date_of_birth DATE,

address TEXT,

phone_number INT

3. Get all the details of "Suresh" from the

student

table in the following format.

Expected Output Format:

name age score

Suresh 21 70

Ans

SELECT

FROM

player

WHERE

name = "Suresh";
Three new students have joined the class. Write a SQL query to add the the following details of
students to the student table.

Ans->>

INSERT INTO

player(name, age, score)

VALUES

("Ram", 28, 125),

("Charan", 25, 173),

("Ravan", 20, 152)

Get all the details of the students from the student table.

Expected Output Format:

name age score

Ram 24 10

Suresh 21 9

--- --- ---

Abns

SELECT

FROM

Player
--Update the age of "Shyam" to 30 in the student table. Note: In this table, we can identify a
student by name.

UPDATE

player

SET

age = 30

WHERE

name = "Shyam";
--Delete player "David" from the student table. Note: In this table, we can identify a student by
name.

DELETE FROM

player

WHERE
 name = "David";
--Add the New Column to store the students branch with column name as the Branch

--ansAlter table student add branch varchar(200);

--Get the Name and Age of the Students From Student Table;
AnsSelect name,age from student;

Rename the column name to full_name in the player table

AnsALTER TABLE

player RENAME COLUMN name TO full_name

Delete Student table from the database;

AnsDROP TABLE PLAYER;

Similar to the e-commerce scenario, we have a database that contains a range of products
with details like the name of the product, category it belongs to, price, brand and rating.
Help the user get the desired products by writing SQL queries satisfying user
requirements.

Note: Expected output format for all the queries, unless specified.

name category price brand rating

Black 90
Clothing Puma 4.8
Shirt 0

Get all the products whose price is less than or equal to 1000.
SELECT

FROM

product

WHERE

price <= 1000;

Get all the best rated products. Note: Products whoserating is

above 4.0 are considered as best rated products.
SELECT
*

FROM

product

WHERE

RATING > 4.0;

Get all details of products from the "Puma" brand.

SELECT

FROM

product

WHERE

brand LIKE "Puma";

User would like to shop for "Jeans". A common pattern in all the
jeans products is that their name ends with "Jeans". Help the user
get the details of required products.
SELECT

FROM

product

WHERE

name LIKE "%jeans";

User wants to purchase "Bourbon Small", "Bourbon With Extra

Cookies", or products from bourbon. Retrieve all details of such
products accordingly.
Hint: All the product whose name starts with "Bourbon".

SELECT
*

FROM

product

WHERE

name LIKE "Bourbon%";

List all the "Chips" in the database. Note: Consider the products
that have "Chips" in the name.
SELECT

FROM

product

WHERE

name LIKE "%chips%";

Get the products whose name matches the following pattern:

 Name endswith at least four characters,
 Out of the last 4 characters, first character is "h" and the third
character is "r".
SELECT

FROM

product

WHERE

name LIKE "%h_r_";

Get all the details of products that belong to "Clothing" category

and price less than 700.
SELECT

*
FROM

PRODUCT

WHERE

CATEGORY = "Clothing"

AND PRICE < 700;

Get all the details of products that belong to "Denim" brand with
rating greater than 4.
SELECT

FROM

product

WHERE

brand = "Denim"

AND rating > 4;

Get all the best-rated products with price less than or equal to
1000. Note: Assume that the products with rating greater than 4.0
as best rated products.
SELECT

FROM

product

WHERE

price <= 1000

AND rating > 4.0;

Get all the products that satisfy all the given conditions
 Rating is above 3.6 and
 price is less than 1000 and
  belongs to "Puma" brand
SELECT

FROM

product

WHERE

rating > 3.6

AND price < 1000

AND brand = "Puma";

Get all the products that belong to any of the following brands.
 Puma
 Denim
 Nike
SELECT

FROM

product

WHERE

brand = "Puma"

OR brand = "Denim"

OR brand = "Nike";

Get all the details of products that follow any of the below
conditions
 brand is "Redmi" with a rating greater than 4
products that belong to the "OnePlus" brand
SELECT
*

FROM

product

WHERE

brand = "Redmi"

AND rating > 4

OR brand = "OnePlus";

Get all the details of the products from the product table which
follows any of the following conditions
 brand is "Puma" and rating greater than 3.5
 brand is "Denim" and rating greater than 4.0
SELECT

FROM

product

WHERE

brand = "Puma"

AND rating > 3.5

OR (

brand = "Denim"

AND rating > 4.0

);

ORDER BY,BETWEEN,IN
Get all the products from "Roadster", "Levi's" or "Puma" brands.
Note: Output must contain rows in the ascending order of price
of the product. If two products are of same price, then sort by
name in the alphabetical order.
SELECT

FROM

product

WHERE

brand IN ("Roadster", "Levi's", "Puma")

ORDER BY

price ASC;

Get all the products that are in the price range of 750 to 1000.
Note:
 Include the products with price 750 and 1000 as well.
 Output must contain rows in the ascending order of price of
the product. If two products are of same price, then sort by
name in the alphabetical order.
SELECT

FROM

product

WHERE

price BETWEEN 750

AND 1000

ORDER BY
price ASC,

name ASC;

User wants to purchase clothes that have high-rating and low-

cost. Get the products from the "Clothing" category as per the user
requirements, i.e.,
 Keep highly-rated products at the top
 Sort the price from low to high
SELECT

FROM

product

WHERE

category = "Clothing"

ORDER BY

rating DESC,

price ASC;

Get all the details of the products from product

table whose
 Brand is Puma or Denim and
 rating
greater than 4
 Get products with lowest price first
SELECT

FROM

product
 WHERE

brand IN ("Puma", "Denim")

AND rating > 4

ORDER BY

price ASC;

Aggregations and groupby and having

Create a table for Player in that take the data as player
name match score fours sixes Year
RCB VS MI

Get the total score of each player.

Note: Output must contain rows in the descending order of
total_score
Expected Output Format :

name total_score

SELECT

name,

sum(score) AS total_score

FROM

player_match_details

GROUP BY
name

ORDER BY

total_score DESC;

Get the total number of 4's hit by each player as no_of_fours

Note: Output must contain rows in the descending order of
no_of_fours,Use Group By Name
Expected Output Format :

name no_of_fours

SELECT

name,

SUM(fours) AS no_of_fours

FROM

player_match_details

GROUP BY

name

ORDER BY

no_of_fours DESC;

Get the highest score of every player as max_score

Note: Output must contain rows in the descending order of
max_score of the player.
Expected Output Format :

name max_score

... ...
 name max_score

SELECT

name,

MAX(score) AS max_score

FROM

player_match_details

GROUP BY

name

ORDER BY

max_score DESC;

Get player name and the total number of matches played as

no_of_matches by each player in the year 2012.
Note: Output must contain rows in the descending order of
no_of_matches of each player.
Expected Output Format :

name no_of_matches

... ...

SELECT

name,

COUNT() AS no_of_matches

FROM

player_match_details
WHERE

year = 2012

GROUP BY

name

ORDER BY

no_of_matches DESC;

Get the year-wise performance, i.e., no_of_matches and

runs_scored by each player.
Note: Output must contain rows in the ascending order of name
&year
Hint:Use Groupby and order by
Expected Output Format :

na ye no_of_mat runs_sc
me ar ches ored

SELECT

name,

year,

count() AS no_of_matches,

SUM(score) AS runs_scored

FROM

player_match_details

GROUP BY

name,

year

ORDER BY

name ASC,
year ASC;

Get the details of all players whose average score is greater than
50 , along with the number of sixes they have scored.
Note: Output must contain rows in the ascending order of
Name of the player.
Expected Output Format :

name avg_score total_sixes

... ... ...

SELECT

name,

AVG(score) AS avg_score,

SUM(sixes) AS total_sixes

FROM

player_match_details

GROUP BY

name

HAVING

avg_score > 50

ORDER BY

name ASC;

Get the second highest salary from the employee table using
LIMIT and OFFSET.
Table: employee
id name salary
1 John 50000
2 Alice 60000
3 Bob 70000
4 Clara 80000
SELECT salary

FROM employee

ORDER BY salary DESC

LIMIT 1 OFFSET 1;

Fetch 3rd to 5th records from the student table.

SELECT *

FROM student

LIMIT 3 OFFSET 2;

JOINS
List all employees along with their department names.
Tables:
employees
emp_id emp_name dept_id
1 Alice 101
2 Bob 102
3 Charlie NULL
departments
dept_id dept_name
101 HR
dept_id dept_name

102 IT
103 Finance
sql
Show the names of students who enrolled in any course.
SELECT c.course_name, e.student_id

FROM courses c

LEFT JOIN enrollments e ON c.course_id = e.course_id;