Database Query using SQL — Question 20
Back to all questionsConsider the CUSTOMERS table having the following records:
Table: CUSTOMERS
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Bengaluru | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
(a) Write an SQL query to display all records in ascending order of name.
(b) Write an SQL query to display all records in descending order of name.
(c) Write an SQL query to display all records in ascending order of name and descending order of age.
(d) Write an SQL query to display maximum salary.
(e) Write an SQL query to display minimum salary.
(f) Write an SQL query to display total number of records.
(g) Write an SQL query to display average salary.
(h) Write an SQL query to display total salary of all the persons.
(i) Write an SQL query to display names of those persons whose salary is greater than the average salary.
(j) Write an SQL query to display details of those persons whose age is less than the average age.
(a)
SELECT * FROM CUSTOMERS
ORDER BY NAME;+----+----------+-----+-----------+--------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+--------+
| 4 | CHAITALI | 25 | MUMBAI | 6500 |
| 5 | HARDIK | 27 | BHOPAL | 8500 |
| 3 | KAUSHIK | 23 | KOTA | 2000 |
| 2 | KHILAN | 25 | DELHI | 1500 |
| 6 | KOMAL | 22 | BENGALURU | 4500 |
| 7 | MUFFY | 24 | INDORE | 10000 |
| 1 | RAMESH | 32 | AHMEDABAD | 2000 |
+----+----------+-----+-----------+--------+
(b)
SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;+----+----------+-----+-----------+--------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+--------+
| 1 | RAMESH | 32 | AHMEDABAD | 2000 |
| 7 | MUFFY | 24 | INDORE | 10000 |
| 6 | KOMAL | 22 | BENGALURU | 4500 |
| 2 | KHILAN | 25 | DELHI | 1500 |
| 3 | KAUSHIK | 23 | KOTA | 2000 |
| 5 | HARDIK | 27 | BHOPAL | 8500 |
| 4 | CHAITALI | 25 | MUMBAI | 6500 |
+----+----------+-----+-----------+--------+
(c)
SELECT *
FROM CUSTOMERS
ORDER BY NAME ASC, AGE DESC;+----+----------+-----+-----------+--------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+--------+
| 4 | CHAITALI | 25 | MUMBAI | 6500 |
| 5 | HARDIK | 27 | BHOPAL | 8500 |
| 3 | KAUSHIK | 23 | KOTA | 2000 |
| 2 | KHILAN | 25 | DELHI | 1500 |
| 6 | KOMAL | 22 | BENGALURU | 4500 |
| 7 | MUFFY | 24 | INDORE | 10000 |
| 1 | RAMESH | 32 | AHMEDABAD | 2000 |
+----+----------+-----+-----------+--------+
(d)
SELECT MAX(SALARY) FROM CUSTOMERS;+-------------+
| MAX(SALARY) |
+-------------+
| 10000 |
+-------------+
(e)
SELECT MIN(SALARY) FROM CUSTOMERS;+-------------+
| MIN(SALARY) |
+-------------+
| 1500 |
+-------------+
(f)
SELECT COUNT(*) FROM CUSTOMERS;+----------+
| COUNT(*) |
+----------+
| 7 |
+----------+
(g)
SELECT AVG(SALARY) FROM CUSTOMERS;+-------------+
| AVG(SALARY) |
+-------------+
| 5000 |
+-------------+
(h)
SELECT SUM(SALARY) FROM CUSTOMERS;+-------------+
| SUM(SALARY) |
+-------------+
| 35000 |
+-------------+
(i)
SELECT NAME
FROM CUSTOMERS
WHERE SALARY > (SELECT AVG(SALARY) FROM CUSTOMERS);+----------+
| NAME |
+----------+
| CHAITALI |
| HARDIK |
| MUFFY |
+----------+
(j)
SELECT * FROM CUSTOMERS
WHERE AGE < (SELECT AVG(AGE) FROM CUSTOMERS);+----+----------+-----+-----------+--------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+--------+
| 2 | KHILAN | 25 | DELHI | 1500 |
| 3 | KAUSHIK | 23 | KOTA | 2000 |
| 4 | CHAITALI | 25 | MUMBAI | 6500 |
| 6 | KOMAL | 22 | BENGALURU | 4500 |
| 7 | MUFFY | 24 | INDORE | 10000 |
+----+----------+-----+-----------+--------+