Database Query using SQL

The Group/Aggregate function works with data of multiple rows at a time and returns aggregated value.

The ORDER BY clause lets you arrange the result set in ascending or descending order.

To specify filtering condition for groups, the HAVING clause is used in MySQL.

By default, the ORDER BY clause sorts the result-set in the Ascending order.

To sort the result set in descending order, DESC keyword is used with ORDER BY.

Two types of MySQL functions are single row functions and Multiple row functions.

The Count(*) function returns the total number of rows, including duplicates and NULL in a table.

Group functions are also known as Aggregate functions.

In MySQL, Distinct option causes a group function to consider only unique values of the argument expression.

The MIN() function returns the lowest value from the given column or expression.

The ORDER BY clause combines all those records that have identical values in a particular field or a group of fields.

Answer

False

Reason — The SQL ORDER BY clause is used to sort data in ascending or descending order based on one or more columns. To group records with identical values, the GROUP BY clause is used.

The WHERE clause is used to specify filtering conditions for groups.

Answer

False

Reason — The HAVING clause is used to specify filtering conditions for groups.

DISTINCT option causes a group function to consider only the unique values of the argument expression.

Answer

True

Reason — The DISTINCT keyword, when used with group functions (such as COUNT, SUM, AVG), ensures that only unique values of the specified column or expression are considered in the calculation, thereby eliminating duplicates from the result set.

By default, ORDER BY clause sorts the result set in descending order.

Answer

False

Reason — By default, ORDER BY clause sorts the result set in ascending order.

COUNT() function ignores null values while counting the records.

Answer

True

Reason — The COUNT() function is used to count the number of non-null values in a column. It ignores any null values present in the column.

The return value of MAX() function is a numeric value.

Answer

False

Reason — The MAX() function returns the largest value in a set of values, which can be numeric, date, or time. Therefore, the return value of the MAX() function is not always a numeric value.

Multiple row function is also known as scalar function.

Answer

False

Reason — Single row function is also known as scalar function.

SUM() function is used to count the total number of records in a table.

Answer

False

Reason — The COUNT() function is used to count the total number of records in a table, while the SUM() function is used to find the total value of a specific column.

Argument type of AVG() function can be numeric or string data type.

Answer

False

Reason — The AVG() function always takes arguments of integer type only.

Group functions can be applied to any numeric values, some text types and DATE values.

Answer

True

Reason — Group functions, also known as aggregate functions in SQL, can be applied to numeric values, some text types, and DATE values. These functions perform calculations across multiple rows and return a single result.

What will be the order of the data being sorted after the execution of given SQL query?

SELECT * FROM STUDENT ORDER BY ROLL_NO;

1. Custom Sort
2. Descending
3. Ascending
4. None of these

Answer

Ascending

Reason — By default, ORDER BY clause sorts the result set in ascending order. Since the query does not include DESC, the data will be sorted in ascending order based on the ROLL_NO column.

Which values will not be considered by SQL while executing the following statement?

SELECT COUNT(column name) FROM DEPARTMENT;

1. Numeric value
2. Text value
3. Null value
4. Date value

Answer

Null value

Reason — The COUNT() function in SQL counts only non-null values in a column. It does not consider null values while executing the statement. Therefore, null values will not be counted by the COUNT() function in the given SQL statement.

Which of the following clauses is used to sort the result set?

1. SORT BY
2. GROUP BY
3. ARRANGE BY
4. ORDER BY

Answer

ORDER BY

Reason — The SQL ORDER BY clause is used to sort data in ascending or descending order based on one or more columns.

Which clause is used in query to place the condition on groups in MySQL?

1. WHERE
2. HAVING
3. GROUP BY
4. Both (i) & (ii)

Answer

HAVING

Reason — The HAVING clause is used in SQL queries to place conditions on groups when using the GROUP BY clause.

If column "Marks" contains the data set {25, 35, 25, 35, 38}, what will be the output after the execution of the given query?

SELECT DISTINCT(MARKS) FROM STUDENTS;

1. 25, 35, 25, 35, 38
2. 25, 25, 35, 35, 38
3. 25, 35, 38
4. 25, 25, 35, 35

Answer

25, 35, 38

Reason — The DISTINCT keyword in the SQL is used to remove duplicate rows from the results of a SELECT statement. Therefore, the output of the query will include each distinct value from the "Marks" column, which are 25, 35, and 38.

If column "Salary" contains the data set {10000,15000,25000,10000,15000}, what will be the output after the execution of the given query?

SELECT SUM(DISTINCT SALARY) FROM EMPLOYEE;

1. 75000
2. 25000
3. 10000
4. 50000

Answer

50000

Reason — The DISTINCT keyword in the SUM() function ensures that only unique values of "Salary" are summed. Therefore, the query will sum up the distinct values of salary, which are 10000, 15000, and 25000, resulting in an output of 50000.

What SQL statement do we use to find the total number of records present in the table Product?

1. SELECT * FROM PRODUCT;
2. SELECT COUNT(*) FROM PRODUCT;
3. SELECT FIND(*) FROM PRODUCT;
4. SELECT SUM() FROM PRODUCT;

Answer

SELECT COUNT(*) FROM PRODUCT;

Reason — The SQL statement SELECT COUNT(*) FROM PRODUCT; is used to retrieve the total number of records or rows present in the "PRODUCT" table. The COUNT(*) function counts all rows along with the NULL values in a specified table, and the SELECT keyword is used to fetch data from the database.

What SQL statement do we use to display the record of all students whose last name contains 5 letters ending with "A"?

1. SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';
2. SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _';
3. SELECT * FROM STUDENTS WHERE LNAME LIKE '????A';
4. SELECT * FROM STUDENTS WHERE LNAME LIKE '*A';

Answer

SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';

Reason — The SQL statement to display the records of all students whose last name contains 5 letters ending with "A" is SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';. This statement uses the LIKE operator with the pattern '_ _ _ _A', where each underscore represents a single character and the letter "A" specifies the ending condition. This pattern ensures that the last name has exactly 5 letters and ends with "A".

Which of the following functions is not an aggregate function?

1. ROUND()
2. SUM()
3. COUNT()
4. AVG()

Answer

ROUND()

Reason — Aggregate functions in SQL include AVG(), COUNT(), MAX(), MIN(), and SUM().

Which of the following functions is used to find the largest value from the given data in MySQL?

1. MAX()
2. MAXIMUM()
3. LARGEST()
4. BIG()

Answer

MAX()

Reason — The MAX() function is used to find the maximum value of a particular column in a table.

Consider a table named Employees with columns — EName, Age, Salary and Designation. Which SQL statement do we use to find out the total salary given to employees?

1. Select SUM(Salary) From Employees;
2. Select COUNT (*) From Employees;
3. Select FIND (*) From Employees;
4. Select SUM () From Employees;

Answer

Select SUM(Salary) From Employees;

Reason — The SQL statement used to find out the total salary given to employees is SELECT SUM(Salary) FROM Employees;. This statement utilizes the SUM() function to calculate the total value of the 'Salary' column from the 'Employees' table.

Which clause in SQL is used to apply a condition on a group?

1. Where
2. Having
3. As
4. On

Answer

Having

Reason — The HAVING clause is used in SQL queries to place conditions on groups when using the GROUP BY clause.

In SQL, which function is used to extract a date from a date expression?

1. Now()
2. Curdate()
3. Date()
4. Day()

Answer

Date()

Reason — In SQL, the DATE() function is used to extract the date part of a date or date-time expression.

Assertion (A): COUNT() function ignores distinct values.

Reasoning (R): Distinct clause ignores duplicate values.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is false but R is true.

Explanation
The COUNT() function in SQL does not ignore distinct values, it counts all occurrences, including distinct values. On the other hand, the DISTINCT keyword in SQL eliminates duplicate values, ensuring that only distinct values are considered.

Assertion (A): The HAVING clause is used with GROUP BY clause and aggregate functions.

Reasoning (R): WHERE clause specifies the condition on individual rows or records.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true but R is not the correct explanation of A.

Explanation
The HAVING clause is used in combination with the GROUP BY clause. It allows aggregate functions to be used along with the specified condition. This is necessary because aggregate functions cannot be used with the WHERE clause, which is evaluated on a single row, whereas aggregate functions are evaluated on a group of rows specified by the GROUP BY clause.

Assertion (A): SQL queries are performed on a table created under a database.

Reasoning (R): The database is a folder created by the user in MySQL workbench. Thereafter, the tables are created in a database where various queries can be performed using SQL commands.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
The database is a folder created by the user in MySQL workbench. Within this database, tables are created, and SQL commands are used to perform various queries on these tables, such as altering tables, inserting data, and retrieving data.

Assertion (A): Aggregate functions in SQL operate on multiple set of values and return a single value as the output.

Reasoning (R): The aggregate functions are used to perform some fundamental arithmetic operations such as MAX(), MIN(), SUM() etc.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
Aggregate functions (multiple-row functions) in SQL operate on multiple sets of values and return a single value as the output. Some common aggregate functions are SUM(), MAX(), MIN(), etc., which are used to perform fundamental arithmetic operations on groups of values.

Assertion (A): A Join is a query that combines rows from two or more tables.

Reasoning (R): Equi-joins are joins based on equality conditions.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true but R is not the correct explanation of A.

Explanation
An SQL JOIN clause is used to combine rows from two or more tables based on a common field between them. An Equi join is a simple SQL join condition that uses the equal sign (=) as a comparison operator to define a relationship between two tables based on a common field.

Assertion (A): The INTERSECT operator returns all rows that are in both result sets.

Reasoning (R): The UNION and INTERSECT yields same output.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is true but R is false.

Explanation
The INTERSECT operator returns all rows that appear in both interconnected tables, while the UNION operator returns all rows from interconnected tables, eliminating duplicate rows. Therefore, the UNION and INTERSECT operators do not yield the same output.

Assertion (A): GROUP BY clause is always preceded by ORDER BY clause in a SELECT statement.

Reasoning (R): ORDER BY clause always follows other clauses.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

Both A and R are true and R is the correct explanation of A.

Explanation
In SQL syntax, the GROUP BY clause comes before the ORDER BY clause. The standard order of clauses is SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. On the other hand, it is true that the ORDER BY clause always follows other clauses, as it comes after the other clauses in the statement.

Assertion (A): All aggregate functions except count(*) ignore null values in their input collection.

Reasoning (R): SUM, AVG, MIN, and MAX can only be used with numeric columns.

1. Both A and R are true and R is the correct explanation of A.
2. Both A and R are true but R is not the correct explanation of A.
3. A is true but R is false.
4. A is false but R is true.

Answer

A is true but R is false.

Explanation
In SQL, aggregate functions like SUM, AVG, MIN, and MAX ignore NULL values in their calculations. The function COUNT(*), however, counts all rows along with NULL values in the table. The SUM and AVG functions can take arguments of only integer type, and can be used with numeric columns. While MAX and MIN functions can be used with various data types, including numeric, string, and datetime columns.

ABC Associates has over 2000 employees on its roll and deals with customer support services. Help the company to perform associated operations for calculating the salaries (stored in Sal column) of their employees and to perform relevant analysis on the fetched data from the SQL database/table.

How can we retrieve records where salary is between 1000 and 2000?

Answer

SELECT * FROM emp 
WHERE Sal BETWEEN 1000 AND 2000;

OR

SELECT * FROM emp 
WHERE Sal >= 1000 AND Sal <= 2000;

Select all records where dept no of both emp and dept table matches.

Answer

SELECT * 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

If there are two tables emp and emp1, and both have common records, how can we fetch all the records but common records only once?

Answer

SELECT * FROM emp 
UNION 
SELECT * FROM emp1;

How can we fetch only common records from two tables emp and emp1?

Answer

SELECT * FROM emp 
INTERSECT 
SELECT * FROM emp1;

How can we retrieve all records of emp that are not present in emp1?

Answer

SELECT * FROM emp 
LEFT JOIN
SELECT * FROM emp1;

Count the total salary deptno wise where more than 2 employees exist.

Answer

SELECT deptno, SUM(sal) AS TOTALSAL
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2; 

Suppose there is annual salary information provided by emp table. How can we fetch monthly salary of each and every employee?

Answer

SELECT ename, Sal / 12 AS MonthlySalary 
FROM emp;

Select all records from emp table where deptno = 10 or 40.

Answer

SELECT * FROM emp 
WHERE deptno = 10 OR deptno = 40;

Select all records from emp table where deptno = 30 and sal > 1500.

Answer

SELECT * FROM emp 
WHERE deptno = 30 AND Sal > 1500;

Count number of managers (MGR column) and their salary in emp table.

Answer

SELECT COUNT(MGR), COUNT(Sal) 
FROM emp;

How can we display all the employees grouped together on the basis of deptno and sal in descending order?

Answer

SELECT ename, deptno, sal 
FROM emp
GROUP BY deptno 
ORDER BY Sal DESC;

What are single row and multiple row functions?

Answer

Single row functions operate on a single row to return a single value per row as the output. They can accept one or more arguments but return only one result per row. They are also known as Scalar Functions.

Multiple row functions operate on a group of rows or a set of values and return a single value calculated from all the values present in a column instead of providing the listing of all of the rows. They are also known as aggregate functions.

What is the significance of GROUP BY clause in an SQL query?

Answer

The GROUP BY clause in SQL can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns. It groups the rows on the basis of the values present in one of the columns and then the aggregate functions are applied on any column of these groups to obtain the result of the query.

What is the difference between WHERE and HAVING clause in SQL select command?

Answer

The WHERE clause in an SQL SELECT command is used to filter individual rows based on specified conditions, such as column values, before any grouping is performed. On the other hand, the HAVING clause is used for filtering groups of rows after grouping has been applied, with aggregate functions like SUM or AVG.

Write a query to find out the sum, average, lowest and highest marks in Student table.

Answer

The Student table is as follows:

SELECT SUM(MARKS) AS Total_Sum, AVG(MARKS) AS Average_Marks, 
MIN(MARKS) AS Lowest_Marks, MAX(MARKS) AS Highest_Marks
FROM Student;

+-----------+---------------+--------------+---------------+
| Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+-----------+---------------+--------------+---------------+
|       742 |       82.4444 |           70 |            98 |
+-----------+---------------+--------------+---------------+

Write a query to find out the sum, average, lowest and highest marks of the students in Student table grouped by STREAM.

Answer

The Student table is as follows:

SELECT STREAM, SUM(MARKS) AS Total_Sum,
AVG(MARKS) AS Average_Marks, MIN(MARKS) AS Lowest_Marks,
MAX(MARKS) AS Highest_Marks
FROM Student
GROUP BY STREAM;

+------------+-----------+---------------+--------------+---------------+
| STREAM     | Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+------------+-----------+---------------+--------------+---------------+
| SCIENCE    |       244 |       81.3333 |           75 |            93 |
| COMMERCE   |       188 |       94.0000 |           90 |            98 |
| HUMANITIES |       158 |       79.0000 |           78 |            80 |
| VOCATIONAL |       152 |       76.0000 |           70 |            82 |
+------------+-----------+---------------+--------------+---------------+

Consider the table "Item" given below and give the outputs on the basis of it:

Table: ITEM

(a) SELECT SUM(Price) FROM ITEM;

(b) SELECT AVG(Price) FROM ITEM;

(c) SELECT MIN(Price) FROM ITEM;

(d) SELECT MAX(Price) FROM ITEM;

(e) SELECT COUNT(Price) FROM ITEM;

(f) SELECT DISTINCT Price FROM ITEM;

(g) SELECT COUNT(DISTINCT Price) FROM ITEM;

(h) SELECT Iname, Price*Quantity FROM ITEM;

Answer

(a) SELECT SUM(Price) FROM ITEM;

+------------+
| SUM(Price) |
+------------+
|        370 |
+------------+

(b) SELECT AVG(Price) FROM ITEM;

+------------+
| AVG(Price) |
+------------+
|    74.0000 |
+------------+

(c) SELECT MIN(Price) FROM ITEM;

+------------+
| MIN(Price) |
+------------+
|         20 |
+------------+

(d) SELECT MAX(Price) FROM ITEM;

+------------+
| MAX(Price) |
+------------+
|        150 |
+------------+

(e) SELECT COUNT(Price) FROM ITEM;

+--------------+
| COUNT(Price) |
+--------------+
|            5 |
+--------------+

(f) SELECT DISTINCT Price FROM ITEM;

+-------+
| Price |
+-------+
|    50 |
|   100 |
|   150 |
|    20 |
+-------+

(g) SELECT COUNT(DISTINCT Price) FROM ITEM;

+-----------------------+
| COUNT(DISTINCT Price) |
+-----------------------+
|                     4 |
+-----------------------+

(h) SELECT Iname, Price*Quantity FROM ITEM;

+-----------+----------------+
| Iname     | Price*Quantity |
+-----------+----------------+
| SOAP      |           5000 |
| POWDER    |           5000 |
| FACECREAM |           3750 |
| PEN       |          10000 |
| SOAPBOX   |           2000 |
+-----------+----------------+

Define a function. Why are they useful?

Answer

A function can be defined as a set of predefined commands which, when called, performs certain operations and returns a single value or a result set.

Functions are useful because they allow for code reusability, readability by breaking down complex tasks into smaller units.

Write commands to display the system date.

Answer

SELECT SYSDATE();

+---------------------+
| SYSDATE()           |
+---------------------+
| 2024-06-14 11:56:41 |
+---------------------+

Write a command to display the name of the current month.

Answer

SELECT MONTHNAME(CURDATE());

+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| June                 |
+----------------------+

Write a command to print the day of the week of your birthday in the year 1999.

Answer

SELECT DAYOFWEEK('1999-07-21');

+-------------------------+
| DAYOFWEEK('1999-07-21') |
+-------------------------+
|                       4 |
+-------------------------+

What is the difference between SYSDATE() and NOW() function?

Answer

The difference between the SYSDATE() and NOW() functions is that the SYSDATE() function returns the exact time at which the function executes. On the other hand, the NOW() function returns the current date and time at the beginning of the statement execution.

Consider two fields—B_date, which stores the birth date, and J_date, which stores the joining date of an employee. Write commands to find out and display the approximate age of an employee as on joining date.

Answer

SELECT (YEAR(J_DATE) - YEAR(B_DATE));

Write a query to find out the number of students in each Stream in STUDENT table.

Answer

The Student table is as follows:

SELECT STREAM, COUNT(*) AS NUMBER_OF_STUDENTS
FROM STUDENT
GROUP BY STREAM;

+------------+--------------------+
| STREAM     | NUMBER_OF_STUDENTS |
+------------+--------------------+
| SCIENCE    |                  4 |
| COMMERCE   |                  2 |
| HUMANITIES |                  2 |
| VOCATIONAL |                  2 |
+------------+--------------------+

Consider the given table Faculty and answer the questions that follow:

Table: FACULTY

(a) To display the details of those Faculty members whose salary is higher than 12000.

(b) To display the details of Faculty members whose salary is in the range of 8000 to 12000 (both values included).

(c) Count the number of different ids from faculty.

(d) Count the number of faculty members getting salary as 10000.

(e) Display details of those faculty members whose names start with S.

(f) Display all records in descending order of Hire date.

(g) Find the maximum and the minimum salary.

(h) Select CONCAT(F_Name, L_Name) from FACULTY;

(i) Select Month(Hire_date) from FACULTY;

Answer

(a)

SELECT * FROM FACULTY WHERE SALARY > 12000;

+------+---------+--------+------------+--------+
| F_ID | F_NAME  | L_NAME | HIRE_DATE  | SALARY |
+------+---------+--------+------------+--------+
|  104 | RAKSHIT | SONI   | 2001-05-18 |  14000 |
+------+---------+--------+------------+--------+

(b)

SELECT * FROM FACULTY WHERE SALARY BETWEEN 8000 AND 12000;

+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+

(c)

SELECT COUNT(DISTINCT F_ID) FROM FACULTY;

+----------------------+
| COUNT(DISTINCT F_ID) |
+----------------------+
|                    5 |
+----------------------+

(d)

SELECT COUNT(*) FROM FACULTY WHERE SALARY = 10000;

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(e)

 SELECT * FROM FACULTY WHERE F_NAME LIKE 'S%';

+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+

(f)

SELECT * FROM FACULTY ORDER BY HIRE_DATE DESC;

+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  104 | RAKSHIT | SONI       | 2001-05-18 |  14000 |
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
+------+---------+------------+------------+--------+

(g)

SELECT MAX(SALARY), MIN(SALARY) FROM FACULTY;

+-------------+-------------+
| MAX(SALARY) | MIN(SALARY) |
+-------------+-------------+
|       14000 |        8000 |
+-------------+-------------+

(h)

+------------------------+
| CONCAT(F_Name, L_Name) |
+------------------------+
| AMITMISHRA             |
| NITINVYAS              |
| RAKSHITSONI            |
| RASHMIMALHOTRA         |
| SULEKHASRIVASTAVA      |
+------------------------+

(i)

+------------------+
| Month(Hire_date) |
+------------------+
|               10 |
|               12 |
|                5 |
|                9 |
|                6 |
+------------------+

Consider the following EMP and DEPT tables:

Table: EMP

Table: DEPT

Write the SQL command to get the following:

(a) Show the minimum, maximum and average salary of managers.

(b) Count the number of clerks in the organization.

(c) Display the designation-wise list of employees with name, salary and date of joining.

(d) Count the number of employees who are not getting commission.

(e) Show the average salary for all departments with more than 5 working people.

(f) List the count of employees grouped by DeptID.

(g) Display the maximum salary of employees in each department.

(h) Display the name of employees along with their designation and department name.

(i) Count the number of employees working in ACCOUNTS department.

Answer

(a)

SELECT MIN(Sal) AS MinSalary, MAX(Sal) AS MaxSalary, AVG(Sal) AS AvgSalary
FROM EMP
WHERE Designation = 'MANAGER';

+-----------+-----------+-----------+
| MinSalary | MaxSalary | AvgSalary |
+-----------+-----------+-----------+
|      2985 |      2985 |      2985 |
+-----------+-----------+-----------+

(b)

SELECT COUNT(*) AS ClerkCount
FROM EMP
WHERE Designation = 'CLERK';

+------------+
| ClerkCount |
+------------+
|          1 |
+------------+

(c)

SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;

+-------------+-----------+------+------------+
| Designation | EmpName   | Sal  | DOJ        |
+-------------+-----------+------+------------+
| CLERK       | SMITH     |  800 | 1990-12-18 |
| MANAGER     | MAHADEVAN | 2985 | 1991-04-02 |
| SALESMAN    | ANYA      | 1600 | 1991-02-20 |
| SALESMAN    | SETH      | 1250 | 1991-02-22 |
+-------------+-----------+------+------------+

(d)

SELECT COUNT(*) AS No_comm
FROM EMP
WHERE comm is NULL;

+---------+
| No_comm |
+---------+
|       2 |
+---------+

(e)

SELECT d.DeptID, d.DeptName, AVG(e.Sal) AS Avg_Salary
FROM DEPT d, EMP e
WHERE e.DeptID = d.DeptID AND d.DeptID IN (
  SELECT DeptID
  FROM EMP
  GROUP BY DeptID
  HAVING COUNT(*) > 5
)
GROUP BY d.DeptID, d.DeptName;

(f)

SELECT DeptID, COUNT(*) AS EmpCount
FROM EMP
GROUP BY DeptID;

+--------+----------+
| DeptID | EmpCount |
+--------+----------+
|     20 |        2 |
|     30 |        2 |
+--------+----------+

(g)

SELECT D.DeptName, MAX(E.Sal) AS MaxSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID
GROUP BY D.DeptName;

+-----------+-----------+
| DeptName  | MaxSalary |
+-----------+-----------+
| PERSONNEL |      2985 |
| ACCOUNTS  |      1600 |
+-----------+-----------+

(h)

SELECT EMP.EMPNAME, EMP.DESIGNATION, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID;

+-----------+-------------+-----------+
| EMPNAME   | DESIGNATION | DEPTNAME  |
+-----------+-------------+-----------+
| SMITH     | CLERK       | PERSONNEL |
| ANYA      | SALESMAN    | ACCOUNTS  |
| SETH      | SALESMAN    | ACCOUNTS  |
| MAHADEVAN | MANAGER     | PERSONNEL |
+-----------+-------------+-----------+

(i)

SELECT COUNT(*) AS NUM_EMP
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID
AND DEPTNAME = 'ACCOUNTS';

+---------+
| NUM_EMP |
+---------+
|       2 |
+---------+

Write a MySQL command for creating a table PAYMENT whose structure is given below:

Table: PAYMENT

Answer

CREATE TABLE PAYMENT (
    Loan_number INT(4) PRIMARY KEY,
    Payment_number VARCHAR(3),
    Payment_date DATE,
    Payment_amount INT(8) NOT NULL
);

Consider the table Product shown below:

Table: PRODUCT

Write the commands in SQL queries for the following:

(a) To display the details of product whose price is in the range of 40 and 120 (both values included).

(b) To increase the price of all the products by 20.

Answer

(a)

SELECT * 
FROM PRODUCT 
WHERE PRICE BETWEEN 40 AND 120;

+------+-------------+--------------+-------+
| P_ID | ProductName | Manufacturer | Price |
+------+-------------+--------------+-------+
| P001 | MOISTURISER | XYZ          |    40 |
| P004 | SHAMPOO     | TAP          |    95 |
+------+-------------+--------------+-------+

(b)

UPDATE PRODUCT 
SET PRICE = PRICE + 20;

Consider the table RESULT given below. Write commands in MySQL for (a) to (d) and output for (e) to (g):

Table: RESULT

(a) To list the names of those students who have obtained Division as FIRST in the ascending order of NAME.

(b) To display a report listing Name, Subject and Annual Stipend received assuming that the Stipend column has monthly stipend.

(c) To count the number of students who have either Accounts or Informatics as subject.

(d) To insert a new row in the table RESULT: 7, "Mohan", 500, "English", 73, "Second"

(e) SELECT AVG(STIPEND) FROM RESULT WHERE DIVISION = "THIRD";

(f) SELECT COUNT(DISTINCT Subject) FROM RESULT;

(g) SELECT MIN(Average) FROM RESULT WHERE Subject = "English";

Answer

(a)

SELECT NAME 
FROM RESULT 
WHERE DIVISION = 'FIRST' 
ORDER BY NAME;

+---------+
| NAME    |
+---------+
| AMAL    |
| ANANDHA |
| VEDANT  |
+---------+

(b)

SELECT NAME, SUBJECT, (STIPEND * 12) AS ANNUAL_STIPEND 
FROM RESULT;

+---------+-------------+----------------+
| NAME    | SUBJECT     | ANNUAL_STIPEND |
+---------+-------------+----------------+
| SHARON  | ENGLISH     |           4800 |
| AMAL    | MATHEMATICS |           8160 |
| VEDANT  | ACCOUNTS    |           6000 |
| SHAKEER | INFORMATICS |           2400 |
| ANANDHA | HISTORY     |           4800 |
| UPASNA  | GEOGRAPHY   |           6600 |
+---------+-------------+----------------+

(c)

SELECT COUNT(*) 
FROM RESULT 
WHERE SUBJECT IN ('ACCOUNTS', 'INFORMATICS');

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(d)

INSERT INTO RESULT 
VALUES(7, "MOHAN", 500, "ENGLISH", 73, "SECOND");

(e)

+--------------+
| AVG(STIPEND) |
+--------------+
|     475.0000 |
+--------------+

(f)

+-------------------------+
| COUNT(DISTINCT Subject) |
+-------------------------+
|                       6 |
+-------------------------+

(g)

+--------------+
| MIN(Average) |
+--------------+
|           38 |
+--------------+

Write the SQL query commands based on the following table:

Table: SCHOOLBUS

(a) To show all information of students where capacity is more than the number of students in order of rtno.

(b) To show area_covered for buses covering more than 20 km, but charge is less than 80000.

(c) To show transporter-wise total no. of students travelling.

(d) To show Rtno, Area_covered and Average cost per student for all routes where average cost per student is — Charges/NoOfStudents

(e) Add a new record with the following data:
(11, "Moti Bagh", 35,32,10, "Kisan Tours", 35000)

(f) Give the output considering the original relation as given below:

1. SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";
2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;
3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";
4. SELECT DISTINCT Transporter FROM SCHOOLBUS;

Answer

(a)

SELECT * 
FROM SCHOOLBUS 
WHERE Capacity > NoOfStudents 
ORDER BY Rtno;

+------+--------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER   | CHARGES |
+------+--------------+----------+--------------+----------+---------------+---------+
|    3 | PITAMPURA    |       60 |           55 |       30 | ANAND TRAVELS |   60000 |
|    4 | ROHINI       |      100 |           90 |       35 | ANAND TRAVELS |  100000 |
+------+--------------+----------+--------------+----------+---------------+---------+

(b)

SELECT Area_Covered 
FROM SCHOOLBUS 
WHERE Distance > 20 AND Charges < 80000;

+--------------+
| Area_Covered |
+--------------+
| PITAMPURA    |
+--------------+

(c)

SELECT Transporter, SUM(NoOfStudents) AS Total_Students 
FROM SCHOOLBUS 
GROUP BY Transporter;

+----------------+----------------+
| Transporter    | Total_Students |
+----------------+----------------+
| SHIVAM TRAVELS |            120 |
| ANAND TRAVELS  |            225 |
| BHALLA CO.     |             60 |
| YADAV CO.      |            190 |
| SPEED TRAVELS  |            160 |
| KISAN TOURS    |            100 |
+----------------+----------------+

(d)

SELECT Rtno, Area_Covered, (Charges / NoOfStudents) AS Average_Cost_Per_Student 
FROM SCHOOLBUS;

+------+---------------+--------------------------+
| Rtno | Area_Covered  | Average_Cost_Per_Student |
+------+---------------+--------------------------+
|    1 | VASANT KUNJ   |                 833.3333 |
|    2 | HAUZ KHAS     |                1062.5000 |
|    3 | PITAMPURA     |                1090.9091 |
|    4 | ROHINI        |                1111.1111 |
|    5 | YAMUNA VIHAR  |                 916.6667 |
|    6 | KRISHNA NAGAR |                1000.0000 |
|    7 | VASUNDHARA    |                 909.0909 |
|    8 | PASCHIM VIHAR |                1375.0000 |
|    9 | SAKET         |                 833.3333 |
|   10 | JANAKPURI     |                 950.0000 |
+------+---------------+--------------------------+

(e)

INSERT INTO SCHOOLBUS 
VALUES (11, 'Moti Bagh', 35, 32, 10, 'Kisan Tours', 35000);

(f)

1. SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";

+---------------+
| SUM(Distance) |
+---------------+
|            50 |
+---------------+

2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;

+-------------------+
| MIN(NoOfStudents) |
+-------------------+
|                40 |
+-------------------+

3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";

+--------------+
| AVG(Charges) |
+--------------+
|   81666.6667 |
+--------------+

4. SELECT DISTINCT Transporter FROM SCHOOLBUS;

+----------------+
| Transporter    |
+----------------+
| SHIVAM TRAVELS |
| ANAND TRAVELS  |
| BHALLA CO.     |
| YADAV CO.      |
| SPEED TRAVELS  |
| KISAN TOURS    |
+----------------+

Consider the CUSTOMERS table having the following records:

Table: CUSTOMERS

(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.

Answer

(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 |
+----+----------+-----+-----------+--------+

Consider the following tables WORKER and PAYLEVEL and answer the questions:

Table: WORKER

Table: PAYLEVEL

(a) To display details of all workers in descending order of DOB.

(b) To display the PLEVEL and number of workers in that PLEVEL.

(c) To display the PLEVEL and number of workers in that PLEVEL whose pay is greater than 15000.

(d) To display NAME and DESIG of those workers, whose PLEVEL is either P001 or P002.

(e) Give the output of the following SQL queries:

1. SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

Answer

(a)

SELECT * 
FROM WORKER 
ORDER BY DOB DESC;

+-------+--------------+------------+--------+------------+------------+
| ECODE | NAME         | DESIG      | PLEVEL | DOJ        | DOB        |
+-------+--------------+------------+--------+------------+------------+
|    12 | CHANDER NATH | OPERATOR   | P003   | 2010-02-22 | 1987-07-12 |
|    11 | SACHIN PATEL | SUPERVISOR | P001   | 2004-09-13 | 1985-08-23 |
|    15 | AMEEN AHMED  | MECHANIC   | P002   | 2006-08-21 | 1984-03-13 |
|    13 | FIZZA        | OPERATOR   | P003   | 2009-06-14 | 1983-10-14 |
|    18 | SANYA        | CLERK      | P002   | 2005-12-19 | 1983-06-09 |
+-------+--------------+------------+--------+------------+------------+

(b)

SELECT PLEVEL, COUNT(*) 
FROM WORKER 
GROUP BY PLEVEL;

+--------+----------+
| PLEVEL | COUNT(*) |
+--------+----------+
| P001   |        1 |
| P003   |        2 |
| P002   |        2 |
+--------+----------+

(c)

SELECT PL.PLEVEL, COUNT(*)   
FROM WORKER W, PAYLEVEL PL  
WHERE W.PLEVEL = PL.PLEVEL AND PL.PAY > 15000   
GROUP BY PL.PLEVEL;

+--------+----------+
| PLEVEL | COUNT(*) |
+--------+----------+
| P001   |        1 |
| P002   |        2 |
+--------+----------+

(d)

SELECT NAME, DESIG 
FROM WORKER 
WHERE PLEVEL IN ('P001', 'P002');

+--------------+------------+
| NAME         | DESIG      |
+--------------+------------+
| SACHIN PATEL | SUPERVISOR |
| AMEEN AHMED  | MECHANIC   |
| SANYA        | CLERK      |
+--------------+------------+

(e)

1. SELECT COUNT(PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;

+---------------+--------+
| COUNT(PLEVEL) | PLEVEL |
+---------------+--------+
|             1 | P001   |
|             2 | P003   |
|             2 | P002   |
+---------------+--------+

2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;

+------------+------------+
| MAX(DOB)   | MIN(DOJ)   |
+------------+------------+
| 1987-07-12 | 2004-09-13 |
+------------+------------+

Consider the tables given below and answer the questions that follow:

Table: EVENT

Table: CELEBRITY

(a) Name the Primary keys in both the tables and the Foreign key in 'Event' table. Can NumPerformers (Number of performers) be set as the Primary key? Give reason.

(b) How many rows will be present in the Cartesian join of the above-mentioned two tables?

(c) Write the commands in SQL:

1. To display EventId, Event name, CelebrityId for only those events that have more than 10 performers.
2. To display CelebrityId and names of celebrities who have "Khan" anywhere in their names.
3. To display names of celebrities and fee charged for those celebrities who charge more than 200000.

Answer

(a) In the EVENT table, the primary key is EventId, and in the CELEBRITY table, the primary key is CelebrityID. The foreign key in the EVENT table is CelebrityID, which references the CelebrityID in the CELEBRITY table.

NumPerformers cannot be set as the primary key because primary keys must uniquely identify each row in the table. NumPerformers can have duplicate values for different events, as multiple events can have the same number of performers.

(b) The EVENT table has 4 rows and the CELEBRITY table has 4 rows. So, the Cartesian join will have: 4 × 4 = 16 rows.

(c)

1.

SELECT EVENTID, EVENT, CELEBRITYID 
FROM EVENT 
WHERE NUMPERFORMERS > 10;

+---------+-----------------+-------------+
| EVENTID | EVENT           | CELEBRITYID |
+---------+-----------------+-------------+
|     102 | PROMOTION PARTY | C103        |
|     103 | ENGAGEMENT      | C102        |
|     104 | WEDDING         | C104        |
+---------+-----------------+-------------+

2.

SELECT CELEBRITYID, CELEBRITYNAME  
FROM CELEBRITY  
WHERE CELEBRITYNAME LIKE '%Khan%';

+-------------+-------------------+
| CELEBRITYID | CELEBRITYNAME     |
+-------------+-------------------+
| C101        | FAIZ KHAN         |
| C103        | NEERA KHAN KAPOOR |
+-------------+-------------------+

3.

SELECT CELEBRITYNAME, FEECHARGED 
FROM CELEBRITY 
WHERE FEECHARGED > 200000;

+-------------------+------------+
| CELEBRITYNAME     | FEECHARGED |
+-------------------+------------+
| SANJAY KUMAR      |     250000 |
| NEERA KHAN KAPOOR |     300000 |
+-------------------+------------+

Consider the following structure of TEACHER and STUDENT table:

Table: TEACHER

Table: STUDENT

Write the SQL commands to get the following:

(a) Show the name of students enrolled in Science stream.

(b) Count the number of students in Commerce stream.

(c) Count the number of teachers in each designation.

(d) Display the maximum pay of teacher who is teaching English.

(e) Display the names of students who are taught by "Anand Mathur".

(f) Display the names and designations of teachers who are teaching a student named "Amit".

(g) Find out the name of the teacher who is getting the highest pay.

(h) Find out the cities of teachers who are teaching Maths.

(i) Find out the name of teacher who is getting the lowest salary among PGTs.

(j) Display the list of students who are taught by PGTs only.

Answer

(a)

SELECT Name FROM STUDENT 
WHERE Stream = 'Science';

(b)

SELECT COUNT(*) FROM STUDENT 
WHERE Stream = 'Commerce';

(c)

SELECT Designation, COUNT(*) AS TeacherCount FROM TEACHER 
GROUP BY Designation;

(d)

SELECT MAX(Pay) FROM TEACHER 
WHERE Subject = 'English';

(e)

SELECT S.Name 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID AND T.TName = 'Anand Mathur';

(f)

SELECT T.TName, T.Designation 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID AND S.Name = 'Amit';

(g)

SELECT TName 
FROM TEACHER 
WHERE Pay = (SELECT MAX(Pay) FROM TEACHER);

(h)

SELECT DISTINCT City 
FROM TEACHER 
WHERE Subject = 'Maths';

(i)

SELECT TName 
FROM TEACHER 
WHERE Designation = 'PGT' AND 
Pay = (SELECT MIN(Pay) FROM TEACHER WHERE Designation = 'PGT');

(j)

SELECT S.Name 
FROM STUDENT S, TEACHER T 
WHERE S.TeacherID = T.TeacherID  AND T.Designation = 'PGT';

Consider the following table and answer the questions that follow:

Table: TEACHER

Write the command/output for the following:

(a) To display all information about the teacher of PGT category.

(b) To list the names of female teachers of Hindi department.

(c) To list names, departments and date of hiring of all the teachers in ascending order of date of joining.

(d) To count the number of teachers in English Department.

(e) Display the department and hire date of all the female teachers whose salary is more than 25000.

(f) Display the list of teachers whose name starts with J.

(g) SELECT COUNT(*) FROM TEACHER WHERE Category = 'PGT';

(h) SELECT AVG(Salary) FROM TEACHER GROUP BY Gender;

Answer

(a)

SELECT * 
FROM TEACHER
WHERE CATEGORY = 'PGT' ;

+----+---------------+------------+------------+----------+--------+--------+
| ID | NAME          | DEPARTMENT | HIREDATE   | CATEGORY | GENDER | SALARY |
+----+---------------+------------+------------+----------+--------+--------+
|  3 | NANDITA ARORA | ENGLISH    | 1980-05-16 | PGT      | F      |  30000 |
+----+---------------+------------+------------+----------+--------+--------+

(b)

SELECT Name 
FROM TEACHER 
WHERE Department = 'Hindi' AND Gender = 'F';

+---------------+
| Name          |
+---------------+
| JASPREET KAUR |
+---------------+

(c)

SELECT NAME, DEPARTMENT, HIREDATE 
FROM TEACHER
ORDER BY HIREDATE;

+------------------+---------------+------------+
| NAME             | DEPARTMENT    | HIREDATE   |
+------------------+---------------+------------+
| DISHA SEHGAL     | MATH          | 1980-03-17 |
| NANDITA ARORA    | ENGLISH       | 1980-05-16 |
| SONALI MUKHERJEE | MATH          | 1980-11-17 |
| JAMES JACOB      | ENGLISH       | 1989-10-16 |
| SAURABH SHARMA   | ART           | 1990-02-12 |
| JASPREET KAUR    | HINDI         | 1990-08-01 |
| TANYA NANDA      | SOCIALSTUDIES | 1994-03-17 |
+------------------+---------------+------------+

(d)

SELECT COUNT(*) 
FROM TEACHER
WHERE DEPARTMENT = 'ENGLISH';

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

(e)

SELECT DEPARTMENT, HIREDATE 
FROM TEACHER
WHERE GENDER = 'F' AND SALARY > 25000;

+------------+------------+
| DEPARTMENT | HIREDATE   |
+------------+------------+
| ENGLISH    | 1980-05-16 |
+------------+------------+

(f)

SELECT Name 
FROM TEACHER  
WHERE Name LIKE 'J%';

+---------------+
| Name          |
+---------------+
| JAMES JACOB   |
| JASPREET KAUR |
+---------------+

(g)

+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

(h)

+-------------+
| AVG(Salary) |
+-------------+
|  24500.0000 |
|  22500.0000 |
+-------------+

Write SQL commands and the output for the following queries:

Table: SPORTS

(a) Display the names of the students who have grade 'A' in either Game1 or Game2 or both.

(b) Display the number of students having game 'Cricket'.

(c) Display the names of students who have the same game for both Game1 and Game2.

(d) Display the games taken by the students whose name starts with 'A'.

(e) Give the output of the following sql statements:

1. SELECT COUNT(*) FROM SPORTS;
2. SELECT DISTINCT CLASS FROM SPORTS;
3. SELECT MAX(Class) FROM SPORTS;
4. SELECT COUNT(*) FROM SPORTS GROUP BY Game1;

Answer

(a)

SELECT NAME FROM SPORTS 
WHERE GRADE1 = 'A' OR GRADE2 = 'A';

+---------+
| NAME    |
+---------+
| SAMEER  |
| SUJIT   |
| VEENA   |
| ARCHANA |
| ARPIT   |
+---------+

(b)

SELECT COUNT(*) FROM SPORTS 
WHERE GAME1 = 'CRICKET' OR GAME2 = 'CRICKET' ;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

(c)

SELECT NAME FROM SPORTS 
WHERE GAME1 = GAME2;

+-------+
| NAME  |
+-------+
| VEENA |
+-------+

(d)

SELECT NAME, GAME1, GAME2 
FROM SPORTS 
WHERE NAME LIKE 'A%';

+---------+------------+-----------+
| NAME    | GAME1      | GAME2     |
+---------+------------+-----------+
| ARCHANA | BASKETBALL | CRICKET   |
| ARPIT   | CRICKET    | ATHLETICS |
+---------+------------+-----------+

(e)

1. SELECT COUNT(*) FROM SPORTS;

+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+

2. SELECT DISTINCT CLASS FROM SPORTS;

+-------+
| CLASS |
+-------+
|     7 |
|     8 |
|     9 |
|    10 |
+-------+

3. SELECT MAX(Class) FROM SPORTS;

+------------+
| MAX(Class) |
+------------+
|         10 |
+------------+

4. SELECT COUNT(*) FROM SPORTS GROUP BY Game1;

+----------+
| COUNT(*) |
+----------+
|        2 |
|        2 |
|        1 |
|        1 |
+----------+

Consider the following table:

Table: ITEM

Write queries based on the table ITEM:

(a) Display the information of all the items.

(b) Display item name and price value.

(c) Display soap information.

(d) Display the item information whose name starts with letter 's'.

(e) Display a report with item number, item name and total price (total price = price * quantity).

(f) SELECT DISTINCT PRICE FROM ITEM;

(g) SELECT COUNT(DISTINCT Price) FROM ITEM;

Answer

(a)

SELECT * FROM ITEM;

+--------+-----------+-------+----------+
| ITEMNO | INAME     | PRICE | QUANTITY |
+--------+-----------+-------+----------+
|    101 | SOAP      |    50 |      100 |
|    102 | POWDER    |   100 |       50 |
|    103 | FACECREAM |   150 |       25 |
|    104 | PEN       |    50 |      200 |
|    105 | SOAPBOX   |    20 |      100 |
+--------+-----------+-------+----------+

(b)

SELECT INAME, PRICE 
FROM ITEM;

+-----------+-------+
| INAME     | PRICE |
+-----------+-------+
| SOAP      |    50 |
| POWDER    |   100 |
| FACECREAM |   150 |
| PEN       |    50 |
| SOAPBOX   |    20 |
+-----------+-------+

(c)

SELECT  * FROM ITEM 
WHERE INAME = 'SOAP';

+--------+-------+-------+----------+
| ITEMNO | INAME | PRICE | QUANTITY |
+--------+-------+-------+----------+
|    101 | SOAP  |    50 |      100 |
+--------+-------+-------+----------+

(d)

SELECT * FROM ITEM 
WHERE INAME LIKE 'S%';

+--------+---------+-------+----------+
| ITEMNO | INAME   | PRICE | QUANTITY |
+--------+---------+-------+----------+
|    101 | SOAP    |    50 |      100 |
|    105 | SOAPBOX |    20 |      100 |
+--------+---------+-------+----------+

(e)

SELECT ITEMNO, INAME, (PRICE * QUANTITY) AS Total_Price 
FROM ITEM;

+--------+-----------+-------------+
| ITEMNO | INAME     | Total_Price |
+--------+-----------+-------------+
|    101 | SOAP      |        5000 |
|    102 | POWDER    |        5000 |
|    103 | FACECREAM |        3750 |
|    104 | PEN       |       10000 |
|    105 | SOAPBOX   |        2000 |
+--------+-----------+-------------+

(f)

+-------+
| PRICE |
+-------+
|    50 |
|   100 |
|   150 |
|    20 |
+-------+

(g)

+-----------------------+
| COUNT(DISTINCT Price) |
+-----------------------+
|                     4 |
+-----------------------+

Write the output produced by the following SQL commands:

(a) SELECT POW(2,3);

(b) SELECT ROUND(123.2345, 2) ,ROUND(342.9234,-1);

(c) SELECT LENGTH("Informatics Practices");

(d) SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), MONTHNAME("1979/11/26"), DAY("1979/11/26");

(e) SELECT LEFT("INDIA", 3), RIGHT("Computer Science", 4);

(f) SELECT MID("Informatics", 3,4), SUBSTR("Practices", 3);

(g) SELECT CONCAT("You Scored", LENGTH("123") , "rank");

(h) SELECT ABS(-67.89);

(i) SELECT SQRT(625) + ROUND(1234.89, -3);

(j) SELECT MOD(56, 8);

Answer

(a)

+-----------+
| POW(2, 3) |
+-----------+
|         8 |
+-----------+

(b)

+--------------------+--------------------+
| ROUND(123.2345, 2) | ROUND(342.9234,-1) |
+--------------------+--------------------+
|             123.23 |                340 |
+--------------------+--------------------+

(c)

+---------------------------------+
| LENGTH("Informatics Practices") |
+---------------------------------+
|                              21 |
+---------------------------------+

(d)

+--------------------+---------------------+-------------------------+-------------------+
| YEAR("1979-11-26") | MONTH("1979-11-26") | MONTHNAME("1979-11-26") | DAY("1979-11-26") |
+--------------------+---------------------+-------------------------+-------------------+
|               1979 |                  11 | November                |                26 |
+--------------------+---------------------+-------------------------+-------------------+

(e)

+------------------+------------------------------+
| LEFT("INDIA", 3) | RIGHT("Computer Science", 4) |
+------------------+------------------------------+
| IND              | ence                         |
+------------------+------------------------------+

(f)

+-------------------------+------------------------+
| MID("Informatics", 3,4) | SUBSTR("Practices", 3) |
+-------------------------+------------------------+
| form                    | actices                |
+-------------------------+------------------------+

(g)

+----------------------------------------------+
| CONCAT("You Scored", LENGTH("123") , "rank") |
+----------------------------------------------+
| You Scored3rank                              |
+----------------------------------------------+

(h)

+-------------+
| ABS(-67.89) |
+-------------+
|       67.89 |
+-------------+

(i)

+--------------------------------+
| SQRT(625) + ROUND(1234.89, -3) |
+--------------------------------+
|                           1025 |
+--------------------------------+

(j)

+------------+
| MOD(56, 8) |
+------------+
|          0 |
+------------+