1.
Write the query to create a table named 'employees' with fields like
empId, empName, empAge, empAddress, and empSalary.
Query:
1. CREATE TABLE employees (
2. empId INT,
3. empName VARCHAR(40),
4. empAge INT,
5. empAddress VARCHAR(40),
6. empSalary INT
7. );
Result: The 'employees' table will look like you can see below.
empId empName empAge empAddress empSalary
2. Write the query to insert values in the above 'employees' table.
Query:
1. INSERT INTO employees
2. VALUES(1, 'Daksh', 25, 'Jaipur', 32000),
3. (2, 'Ananya', 30, 'Bhopal', 37000),
4. (3, 'Aarush', 32, 'Jaipur', 35000),
5. (4, 'Reyansh', 30, 'Bhopal', 40000)
6. (5, 'Jhalak', 28, 'Jaipur', 39000);
Result: After inserting values in the 'employees' table, it will look like you
can see below.
empId empName empAge empAddress empSalary
1 Daksh 25 Jaipur 32000
2 Ananya 30 Bhopal 37000
3 Aarush 32 Jaipur 35000
4 Reyansh 30 Bhopal 40000
5 Jhalak 28 Jaipur 39000
3. Consider the 'employees' table and write the query to delete record of
'empId=1' from the 'employees' table:
Query:
1. DELETE FROM employees WHERE empId=1;
Result: The above statement will delete the record of 'empId=1' from the
'employees' table.
empId empName empAge empAddress empSalary
2 Ananya 30 Bhopal 37000
3 Aarush 32 Jaipur 35000
4 Reyansh 30 Bhopal 40000
5 Jhalak 28 Jaipur 39000
4. Consider the 'employees' table and write the query to update the age of
'empId=5' in the 'employees' table:
Query:
1. UPDATE employees SET empAge = 29 WHERE empId=5;
Result: The above statement will update the age of 'empId=5' in the
'employees' table.
empId empName empAge empAddress empSalary
1 Daksh 25 Jaipur 32000
2 Ananya 30 Bhopal 37000
3 Aarush 32 Jaipur 35000
4 Reyansh 30 Bhopal 40000
5 Jhalak 28 Jaipur 39000
5. Consider the 'employees' table and write the query to select all records
from the 'employees' table:
Query:
1. SELECT * FROM employees;
Result: As you can see below, above statement will select all records
from the 'employees' table.
empId empName empAge empAddress empSalary
1 Daksh 25 Jaipur 32000
2 Ananya 30 Bhopal 37000
3 Aarush 32 Jaipur 35000
4 Reyansh 30 Bhopal 40000
5 Jhalak 28 Jaipur 39000
6. Consider the 'employees' table and write the query to select the
'empName' and 'empSalary' columns from the 'employees' table:
Query:
1. SELECT empName, empSalary FROM employees;
Result: The above statement will select the 'empName' and 'empSalary'
columns from the 'employees' table.
empName empSalary
Daksh 32000
Ananya 37000
Aarush 35000
Reyansh 40000
Jhalak 39000
7. Consider the 'employees' table and write the query to select distinct
values from the 'empAddress' column in the 'employees' table:
Query:
1. SELECT DISTINCT empAddress FROM employees;
Result: The above statement will select the distinct value from the
'empAddress' column in the 'employees' table.
empAddress
Jaipur
Bhopal
8. Consider the 'employees' table and write the query to filter and select
those records where empAddress='Jaipur' using a WHERE clause:
Query:
1. SELECT * FROM employees
2. WHERE empAddress='Jaipur';
Result: The above statement will filter the records from the 'employees'
table.
empId empName empAge empAddress empSalary
1 Daksh 25 Jaipur 32000
3 Aarush 32 Jaipur 35000
5 Jhalak 28 Jaipur 39000
9. Consider the 'employees' table and write the query to sort records of
the 'employees' table in descending order.
Query:
1. SELECT * FROM employees
2. ORDER BY empName DESC;
Result: The above statement will sort the records of the 'employees'
table in descending order.
empId empName empAge empAddress empSalary
4 Reyansh 30 Bhopal 40000
5 Jhalak 28 Jaipur 39000
1 Daksh 25 Jaipur 32000
2 Ananya 30 Bhopal 37000
3 Aarush 32 Jaipur 35000
10. Consider the 'employees' table and write the query to sort records of
the 'employees' table in ascending order.
Query:
1. SELECT * FROM employees
2. ORDER BY empName ASC;
Result: The above statement will sort the records of the 'employees'
table in ascending order.
empId empName empAge empAddress empSalary
3 Aarush 32 Jaipur 35000
2 Ananya 30 Bhopal 37000
1 Daksh 25 Jaipur 32000
5 Jhalak 28 Jaipur 39000
4 Reyansh 30 Bhopal 40000
11. Consider the 'employees' table and write the query to count the
number of rows in the 'employees' table.
Query:
1. SELECT COUNT(*) FROM employees;
Result: The above statement will count the number of rows in the
'employees' table.
COUNT(*)
12. Consider the 'employees' table and write the query to retrieve all
employees whose salary is between 35000 and 40000 in the 'employees'
table.
Query:
1. SELECT empSalary FROM employees
2. WHERE empSalary
3. BETWEEN 35000 AND 40000;
Result: The above statement will retrieve all employees whose salary is
between 35000 and 40000 in the 'employees' table.
empId empName empAge empAddress empSalary
2 Ananya 30 Bhopal 37000
3 Aarush 32 Jaipur 35000
4 Reyansh 30 Bhopal 40000
5 Jhalak 28 Jaipur 39000
13. Consider the 'employees' table and write the query to add another
column called 'Department'.
Query:
1. ALTER TABLE employees ADD Department VARCHAR(45);
Result: As no value has been inserted in the new column 'Department' so
by default it contains NULL.
empId empName empAge empAddres empSalar Departmen
s y t
1 Daksh 25 Jaipur 32000 NULL
2 Ananya 30 Bhopal 37000 NULL
3 Aarush 32 Jaipur 35000 NULL
4 Reyansh 30 Bhopal 40000 NULL
5 Jhalak 28 Jaipur 39000 NULL
14. Consider a table called 'products' with fields such as product_id,
product_name, product_quantity, and product_price.
product_id product_name product_quantit product_price
y
1 Speaker 2 10000
2 Printer 1 21000
3 Key Board 5 2500
4 Refill Cartridge 6 1500
5 CD Drive 4 2000
i) Write the query to calculate the sum of values in the 'product_price'
column in the 'products' table.
Query:
1. SELECT SUM(product_price)
2. FROM products;
Result:
SUM(product_price)
37000
ii) Write the query to calculate the average value of the 'product_price'
column in the 'products' table.
Query:
1. SELECT AVG(product_price)
2. FROM products;
Result:
AVG(product_price)
7400
iii) Write the query to retrieve the minimum value from the 'product_price'
column in the 'products' table.
Query:
1. SELECT MIN(product_price)
2. FROM products;
Result:
MIN(product_price)
1500
iv) Write the query to retrieve the maximum value from a 'product_price'
column in the 'products' table.
Query:
1. SELECT MAX(product_price)
2. FROM products;
Result:
MAX(product_price)
21000
V) Write the query to group records and calculate aggregate functions for
the 'product_price' column in the 'products' table.
Query:
1. SELECT product_price, COUNT(product_price), AVG(product_price)
2. FROM products GROUP BY product_price;
Result:
product_price COUNT(product_price) Age(product_price)
10000 1 10000
21000 1 21000
2500 1 2500
1500 1 1500
2000 1 2000
15. Consider a table called 'mobilephones' with fields such as Id, Name,
Company, Quantity, and Price.
Id Name Company Colour Quantity Price
Samsung
1 Samsung Blue 1 20000
Galaxy A23
iPhone 13
2 Apple Pink 2 65000
mini
3 iPhone 12 Apple Black 1 54000
Motorola
Viva
4 Edge 30 Motorola 2 38000
Magenta
Fusion
Samsung
5 Galaxy Z Samsung Black 4 48000
Flip3 5G
i) Write the query to select all customers from a 'Colour' column starting
with "bl" in the 'mobilephones' table.
Query:
1. SELECT * FROM mobilephones
2. WHERE Colour LIKE 'bl%';
Result:
product_price COUNT(product_price) Age(product_price)
10000 1 10000
21000 1 21000
2500 1 2500
1500 1 1500
2000 1 2000
15. Consider a table called 'mobilephones' with fields such as Id, Name,
Company, Quantity, and Price.
Id Name Company Colour Quantity Price
Samsung
1 Samsung Blue 1 20000
Galaxy A23
3 iPhone 12 Apple Black 1 54000
Samsung
5 Galaxy Z Samsung Black 4 48000
Flip3 5G
ii) Write the query to select all mobile names whose average is greater
than 45000 from the 'Price' column in the 'mobilephones' table.
Query:
1. SELECT Name, AVG(Price)
2. FROM mobilephones
3. GROUP BY Name HAVING AVG(Price) > 45000;
Result:
Name AVG(Price)
iPhone 13 mini 65000
iPhone 12 54000
Samsung Galaxy Z Flip3 5G 48000
iii) Write the query to select all mobile names whose sum is smaller than
45000 from a 'Price' column in the 'mobilephones' table.
Query:
1. SELECT Name, SUM(Price)
2. FROM mobilephones
3. GROUP BY Name HAVING SUM(Price) < 45000;
Result:
Name SUM(Price)
Samsung Galaxy A23 20000
Motorola Edge 30 Fusion 38000
16. Consider the 'customers' table with Id, firstName, middleName,
lastName, and Address.
Id firstName middleName lastName Address
1 Shruti NULL Bansal Goa
2 NULL Raj Singh Goa
3 Aadi Kumar NULL Goa
4 Nishtha NULL NULL Mumbai
5 NULL NULL Kapoor Mumbai
i) Write the query to return the first non-null value from the firstName,
middleName, and lastName columns.
Query:
1. SELECT Id, Address COALESCE(firstName, middleName, lastName)
AS NAME
2. FROM customers;
Result:
Id Address NAME
1 Goa Shruti
2 Goa Raj
3 Goa Aadi
4 Mumbai Nishtha
5 Mumbai Kapoor
ii) Write the query to delete all rows from a 'customers' table, but keeps
the table structure.
Query:
1. TRUNCATE TABLE customers;
Result: As you can see, the above statement has deleted all rows but left
with only the table structure.
Id firstName middleName lastName Address
17. Consider the two tables: one is the 'teachers' table which has fields
such as teachers_id, Name, teachers_age, and teachers_address. The
other is the 'students' table which has fields such as students_id, Name,
students_age, and students_address.
The following is the 'teachers' table:
teachers_id Name teachers_age teachers_addres
s
100 Karishma 35 Noida
101 Yasha 29 Meerut
102 Kartik 40 Noida
103 Milan 36 Lucknow
The following is the 'students' table:
students_id Name students_age students_addres
s
200 Harsh 19 Noida
201 Palak 18 Lucknow
202 Himanshi 20 Roorkee
203 Ansh 19 Roorkee
i) Write the query to combine the 'Name' column in the 'teachers' table
with the 'Name' column in the 'students' table using the UNION operator.
Query:
1. SELECT Name FROM teachers
2. UNION SELECT Name FROM students;
Result:
Name
Karishma
Yasha
Kartik
Milan
Harsh
Palak
Himanshi
Ansh
ii) Write the query to find the teacher's name whose age is between 34
and 41 in the 'teachers' table.
Query:
1. SELECT * FROM teachers
2. WHERE teachers_age
3. BETWEEN '34' AND '41';
Result:
teachers_id Name teachers_age teachers_addres
s
100 Karishma 35 Noida
102 Kartik 40 Noida
103 Milan 36 Lucknow
