Querying Using SQL

Can you arrange the result set of an SQL query on multiple columns ?

Answer

Yes, we can arrange the result set of an SQL query on multiple columns. We should specify the multiple column names in the ORDER BY clause along with the desired sort order.

For example, the following statement will sort the records in the data table first by the section column in ascending order and then by the marks column in descending order.

SELECT * FROM data
ORDER BY section ASC, marks DESC;

What is the significance of "ORDER BY" in the given query?

SELECT emp_id, fname, lname
FROM person
ORDER BY emp_id;

(a) Data of table person on the basis of column emp_id will be sorted in descending order

(b) Data of table person on the basis of column emp_id will be sorted in ascending order

(c) Only data of column emp_id will be sorted in descending order

(d) Only data of column emp_id will be sorted in ascending order

Answer

Data of table person on the basis of column emp_id will be sorted in ascending order

Reason — The significance of the ORDER BY clause in the given query is to sort the result set based on the values in the emp_id column in ascending order by default.

What will be the order of sorting in the given query?

SELECT emp_id, emp_name 
FROM person
ORDER BY emp_id, emp_name;

(a) Firstly on emp_id and then on emp_name

(b) Firstly on emp_name and then on emp_id

(c) Firstly on emp_id but not on emp_name

(d) None of the mentioned

Answer

Firstly on emp_id and then on emp_name

Reason — The ORDER BY emp_id, emp_name clause sorts the result set firstly by the emp_id column. If there are rows with the same emp_id, those rows are further sorted by the emp_name column.

If column emp_id contains the following set {9, 7, 6, 4, 3, 1, 2}, what will be the output on execution of the given query?

SELECT emp_id 
FROM person 
ORDER BY emp_id;

(a) {9, 7, 6, 4, 3, 1, 2}

(b) {1, 2, 3, 4, 6, 7, 9}

(c) {2, 1, 4, 3, 7, 9, 6}

(d) None of these

Answer

{1, 2, 3, 4, 6, 7, 9}

Reason — The ORDER BY clause in SQL sorts the result set based on the specified column(s) in ascending order by default. In the given query, the emp_id column is specified in the ORDER BY clause. Therefore, the query will sort the emp_id values in ascending order.

Which function can you use with ORDER BY clause to specify custom sort order ?

(a) SORT()

(b) CUSTOM()

(c) FIELD()

(d) All of these

Answer

FIELD()

Reason — The FIELD() function in SQL allows to specify a custom sort order by listing the values in the order we want them to appear.
For example, the following statement selects column fruit_name from the fruits table and orders the result set based on the custom sort order specified by the FIELD() function.

SELECT * FROM fruits
ORDER BY FIELD(fruit_name, 'Apple', 'Banana', 'Orange', 'Grapes');

By default, ORDER BY clause lists the results in ............... order.

1. Descending
2. Any
3. Same
4. Ascending

Answer

Ascending

Reason — By default, ORDER BY clause lists the results in ascending order.

Which one of the following would arrange the rows in ascending order in SQL ?

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

Answer

ORDER BY

Reason — The ORDER BY clause in SQL is used to sort the result set in either ascending or descending order.

To create summary results, ............... clause is used.

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

Answer

GROUP BY

Reason — The GROUP BY clause in SQL is used to create summary results by grouping together all records that share identical values in a particular field or a group of fields.

Consider the following query

SELECT * FROM employee ORDER BY salary ............... , name ............... ;

To display the salary from greater to smaller and name in alphabetical order which of the following options should be used ?

1. Ascending, Descending
2. Asc, Desc
3. Desc, Asc
4. Descending, Ascending

Answer

Desc, Asc
Descending, Ascending

Reason — To display the salary from greater to smaller i.e., in descending order we use DESC or descending keyword and to sort name in alphabetical order i.e., in ascending order we use ASC or ascending keyword.

Select correct SQL query from below to find the temperature in increasing order of all cities.

1. SELECT city FROM weather ORDER BY temperature ;

2. SELECT city, temperature FROM weather ;

3. SELECT city, temperature FROM weather ORDER BY temperature ;

4. SELECT city, temperature FROM weather ORDER BY city ;

Answer

SELECT city, temperature FROM weather ORDER BY city ;

Reason — The query SELECT city, temperature FROM weather ORDER BY city ; selects the city and temperature columns from the weather table and sorts the result set by the city column in ascending order.

Which SQL function is used to count the number of rows in a SQL query ?

1. COUNT()
2. NUMBER()
3. SUM()
4. COUNT(*)

Answer

COUNT(*)

Reason — The SQL function COUNT(*) is used to count the number of rows in an SQL query, including duplicates and rows with NULL values.

Which SQL statement do we use to find out the total number of records present in the table ORDERS ?

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

Answer

SELECT COUNT(*) FROM ORDERS ;

Reason — The COUNT() function in SQL counts the number of rows or records in a table. When used with the asterisk (*), it counts all rows in the table, including duplicates and nulls. Therefore, SELECT COUNT(*) FROM ORDERS will return the total number of records in the ORDERS table.

With SQL, how can you return the number of not null records in the Project field of "Students" table ?

1. SELECT COUNT(Project) FROM Students
2. SELECT COLUMNS(Project) FROM Students
3. SELECT COLUMNS(*) FROM Students
4. SELECT COUNT(*) FROM Students

Answer

SELECT COUNT(Project) FROM Students

Reason — The COUNT() function in SQL counts the number of non-null records in a column. By using COUNT(Project), we are counting the number of rows where the Project column is not null in the student table.

Which of the following is not an aggregate function ?

1. Avg
2. Sum
3. With
4. Min

Answer

With

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

All aggregate functions except ............... ignore null values in their input collection.

1. Count(attribute)
2. Count(*)
3. Avg
4. Sum

Answer

Count(*)

Reason — All aggregate functions, except for COUNT(*), ignore null values in their input collection. COUNT(*) returns all rows, including duplicates and nulls.

Which of the following group functions ignore NULL values ?

1. MAX
2. COUNT
3. SUM
4. All of these

Answer

All of these

Reason — All aggregate functions, except for COUNT(*), ignore null values in their input collection.

The HAVING clause does which of the following ?

1. Acts EXACTLY like a WHERE clause.
2. Acts like a WHERE clause but is used for columns rather than groups.
3. Acts like a WHERE clause but is used for groups rather than rows.
4. Acts like a WHERE clause but is used for rows rather than columns.

Answer

Acts like a WHERE clause but is used for groups rather than rows.

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows.

Aggregate functions can be used in the select list or the ............... clause of a select statement. They cannot be used in a ............... clause.

1. Where, having
2. Having, where
3. Group by, having
4. Group by, where

Answer

Having, where

Reason — Aggregate functions can be used in the select list or the HAVING clause of a select statement. But they cannot be used in a WHERE clause. The reason for this is that the WHERE clause filters rows before any grouping or aggregation occurs, while HAVING clause applies conditions to groups after the data has been grouped using the GROUP BY clause.

SQL applies conditions on the groups through ............... clause after groups have been formed.

1. Group by
2. With
3. Where
4. Having

Answer

Having

Reason — The HAVING clause applies conditions to groups after the data has been grouped using the GROUP BY clause in SQL queries.

Which clause is used with "aggregate functions" ?

1. GROUP BY
2. SELECT
3. WHERE
4. Both (a) and (c)

Answer

GROUP BY

Reason — Aggregate functions (such as COUNT, SUM, AVG, MAX, MIN) are used with the GROUP BY clause to perform operations on each group of rows. The GROUP BY clause groups rows that have the same values in specified columns into summary rows.

What is the meaning of "HAVING" clause in SELECT query ?

1. To filter out the summary groups
2. To filter out the column groups
3. To filter out the row and column values
4. None of the mentioned

Answer

To filter out the summary group

Reason — The HAVING clause is used to filter out summary groups in a SELECT query that involves aggregate functions and the GROUP BY clause.

Where and Having clauses can be used interchangeably in SELECT queries ?

1. True
2. False
3. Only in views
4. With order by

Answer

False

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows. While WHERE conditions cannot include aggregate functions, HAVING conditions can include aggregate functions. Hence, WHERE and HAVING clauses cannot be used interchangeably in SELECT queries.

Raj, a Database Administrator, needs to display the average pay of workers from those departments which have more than five employees. He is experiencing a problem while running the following query :

SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;

Which of the following is a correct query to perform the given task ?

1. SELECT DEPT, AVG(SAL) FROM EMP WHERE COUNT(*) > 5 GROUP BY DEPT;
2. SELECT DEPT, AVG(SAL) FROM EMP HAVING COUNT(*) > 5 GROUP BY DEPT;
3. SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT WHERE COUNT(*) > 5;
4. SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5;

Answer

SELECT DEPT, AVG(SAL) FROM EMP GROUP BY DEPT HAVING COUNT(*) > 5;

Reason — In the above query, the WHERE clause cannot be used with aggregate functions like COUNT(*) because it is meant to filter individual rows before the aggregation. On the other hand, the HAVING clause filters the groups created by the GROUP BY clause to include only those departments (DEPT) that have more than five employees.

To sort the rows of the result table, the ORDER BY clause is specified.

Columns can be sorted in descending sequence by using the SQL keyword DESC.

The SQL built-in function SUM totals values in numeric columns.

The SQL built-in function AVG computes the average of values in numeric columns.

The SQL built-in function MAX obtains the largest value in a numeric column.

The SQL built-in function MIN obtains the smallest value in a numeric column.

The SQL built-in function COUNT computes the number of rows in a table.

The SELECT clause GROUP BY is used to collect those rows that have the same value in a specified column.

With GROUP BY queries, aggregate functions are used.

To specify a condition on groups created by GROUP BY, HAVING clause is used.

The rows of the result relation produced by a SELECT statement can be sorted, but only by one column.

Answer

False

Reason — In SQL, the ORDER BY clause is used to sort the rows of the result relation produced by a SELECT statement. It allows sorting by one or more columns in ascending or descending order.

The HAVING clause acts like a WHERE clause, but it identifies groups that meet a criterion, rather than rows.

Answer

True

Reason — The HAVING clause in SQL is used to filter groups based on specified conditions, while the WHERE clause filters individual rows. This means that the HAVING clause works with grouped data, applying conditions to groups that meet certain criteria, whereas the WHERE clause applies conditions to individual rows before any grouping occurs.

The qualifier DISTINCT must be used in an SQL statement when we want to eliminate duplicate rows.

Answer

True

Reason — The DISTINCT keyword in SQL is used to eliminate duplicate rows from the results of a query. Therefore, when a user wants to ensure that only unique rows are returned, they must use the DISTINCT qualifier in their SQL statement.

DISTINCT and its counterpart, ALL, can be used more than once in a SELECT statement.

Answer

False

Reason — In SQL, both DISTINCT and ALL keywords can be specified only once in a given SELECT clause.

DISTINCT and its counterpart, ALL, can be used together on single field in a SELECT statement.

Answer

False

Reason — In SQL, DISTINCT and ALL cannot be used together on a single field in a SELECT statement. As DISTINCT eliminates duplicate rows from the results, while ALL includes all rows, including duplicates. Therefore, attempting to use them together on the same field would result in a syntax error.

COUNT(field_name) tallies only those rows that contain a value; it ignores all null values.

Answer

True

Reason — When we use COUNT(fieldname), it counts only the rows where the specified field (fieldname) is not null. It does ignore null values for that specific field during counting.

SUM, AVG, MIN, and MAX can only be used with numeric columns.

Answer

True

Reason — The aggregate functions SUM, AVG, MIN, and MAX are designed to work with numeric columns in SQL. They expect numeric values as arguments and return numeric results.

The SQL statement : SELECT Salary + Comm AS Total FROM Emp; adds two fields salary and comm from each row together and lists the results in a column named Total.

Answer

True

Reason — The SQL statement SELECT Salary + Comm AS Total FROM Emp; adds the values of the Salary and Comm columns from each row together and lists the results in a column named Total. This is achieved by using the "+" operator to perform arithmetic addition on the values of the specified fields.

ORDER BY can be combined with the SELECT statements.

Answer

True

Reason — The ORDER BY can be combined with the SELECT statements in SQL to sort query results based on one or more columns.

The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.

Answer

True

Reason — The SQL keyword GROUP BY clause instructs the DBMS to combine all those rows that have identical values in a particular column or a group of columns.

Assertion. The ORDER BY clause of SELECT statement is carried out in the last after executing other clauses of the SELECT statement.

Reason. The ORDER BY clause is carried out on the final result of the SELECT query.

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, the execution order of a SELECT statement is such that the ORDER BY clause is processed last, after all other clauses like FROM, WHERE, GROUP BY, HAVING, and SELECT have been executed. This is because the ORDER BY clause sorts the final result set produced by these earlier steps.

Assertion. MOD() and MIN() are numeric functions, yet they are different types of functions.

Reason. MOD() is a single-row function and MIN() is a group function.

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
MOD() and MIN() are numeric functions, but they are different types. MOD() is a single-row function, meaning it operates on individual rows and returns a result for each row. In contrast, MIN() is a group function (an aggregate function), which operates on a set of rows and returns a single result for the entire set.

Assertion. The GROUP BY clause can use any type of function.

Reason. The GROUP BY clause combines a number of rows in a group and applies functions on it.

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 GROUP BY clause uses aggregate functions. It combines all those records that have identical values in a particular field or a group of fields and applies group-functions, resulting in one summary record per group.

Assertion. The GROUP BY clause yields summary results using group functions.

Reason. The GROUP BY clause combines a number of rows in a group and applies functions on it.

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 GROUP BY clause yields summary results using group functions because it combines all records that have identical values in a particular field or a group of fields. This grouping results in one summary record per group if group functions are used with it.

Assertion. Both WHERE and HAVING clauses work with GROUP BY in a SELECT statement.

Reason. The WHERE clause is applied before forming groups of rows and HAVING clause is applied after forming the groups.

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
Both the WHERE and HAVING clauses can be used with the GROUP BY clause in a SELECT statement to filter and specify conditions for rows and groups, respectively. The WHERE clause filters rows before they are grouped, while the HAVING clause filters groups of rows after they have been grouped by the GROUP BY clause. This is because WHERE conditions cannot include aggregate functions, whereas HAVING conditions can include them.

Assertion. Both WHERE and HAVING clauses are used for specifying conditions.

Reason. WHERE and HAVING clauses of the SELECT query can be used interchangeably.

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
Both WHERE and HAVING clauses are used to specify conditions in a SELECT query, they operate at different levels. The WHERE clause filters rows based on conditions applied to individual rows before grouping, while the HAVING clause filters groups based on conditions applied to the result of aggregation functions after grouping. Hence, they are not interchangeable.

Assertion. Both WHERE and HAVING clauses are used for specifying conditions.

Reason. The WHERE condition is applicable on individual rows and HAVING condition is applicable on a group of rows.

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
Both WHERE and HAVING clauses are used to specify conditions in a SELECT query, they operate at different levels. The WHERE clause filters rows based on conditions applied to individual rows before grouping, while the HAVING clause filters groups based on conditions applied to the result of aggregation functions after grouping.

Anjali writes the following commands with respect to a table employee having fields, empno, name, department, commission :

Command1: Select count(*) from employee;
Command2: Select count(commission) from employee;

She gets the output as 4 for the first command but gets an output 3 for the second command. Explain the output with justification.

Answer

The difference in output is due to how COUNT() function behaves with different types of data in the specified column.

Command1: Select count(*) from employee; : This command counts all rows in the employee table, regardless of whether any column contains NULL values or not. When we specify an asterisk (*) with COUNT(), it returns the total number of rows in the table, including duplicates and NULL values. So, it returns the total number of rows in the table, which is 4.

Command2: Select count(commission) from employee; : If we specify argument (expr) in COUNT() function, it returns rows where expr is not null. This command counts the number of non-NULL values in the commission column. Therefore, it returns a count of 3 instead of 4 due to the presence of a NULL value in that column in one of the rows.

What is the use of ORDER BY clause ?

Answer

In SQL, the ORDER BY clause is used to sort the rows of the result set produced by a SELECT statement. We can specify one or more columns in the ORDER BY clause to sort the result set either in ascending (default) or descending order.

What is the default sort order of ORDER BY clause ?

Answer

The default sort order of ORDER BY clause is ascending order.

Write an example query that sorts on three columns.

Answer

SELECT * FROM DATA
ORDER BY marks ASC, section DESC, term ASC; 

Write a query that sorts the data of table student on the basis of Project-Group (in ascending order), section (in descending order), Marks (in descending order).

Answer

SELECT *
FROM student
ORDER BY Project_Group ASC, Section DESC, Marks DESC;

What is the difference between HAVING and WHERE clause ?

Answer

What is the use of GROUP BY clause ?

Answer

The GROUP BY clause in SQL is used to combine all records that have identical values in a particular field or group of fields. This grouping results in one summary record per group if group functions, such as aggregate functions, are used with it.

What are aggregate functions? What is their use? Give some examples.

Answer

Aggregate functions in SQL work with data from multiple rows at a time and return a single aggregated value. They are used to perform calculations across multiple rows and return a summary result for that group.

Examples of aggregate functions include SUM(), COUNT(), MAX(), MIN(), AVG() etc.

What type of functions can you use with GROUP BY and HAVING clauses ?

Answer

Aggregate functions are used with the GROUP BY and HAVING clauses to apply conditions on grouped data.

Harjat has created the table EMP in his database.

Table : EMP

Now he wants to find the sum of commission earned by each department. He has executed the following query :

SELECT dept, sum(comm)
GROUP BY dept
FROM EMP;

But, he got an error. Rewrite the correct query after identifying the error(s).

Answer

The error in Harjat's query is the order of the clauses. The GROUP BY clause should come after the FROM clause. The corrected query is as follows :

SELECT dept, sum(comm)
FROM EMP
GROUP BY dept;

Table BOOK_INFORMATION

Which SQL statement allows you to find the highest price from the table BOOK_INFORMATION?

1. SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION;
2. SELECT MAX(PRICE) FROM BOOK_INFORMATION;
3. SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION;
4. SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC;

Answer

SELECT MAX(PRICE) FROM BOOK_INFORMATION;

1. SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION; — This query selects the BOOK_ID, BOOK_TITLE, and maximum PRICE from the BOOK_INFORMATION table. However, the requirement is to find the highest price only.
2. SELECT MAX(PRICE) FROM BOOK_INFORMATION; — This query selects the maximum PRICE from the BOOK_INFORMATION table using the MAX() aggregate function. This option is correct because it directly retrieves the highest price among all the books listed in the BOOK_INFORMATION table, which is what the question asks for.
3. SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION; — There is no MAXIMUM() function in SQL.
4. SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC; — This query selects all prices from the BOOK_INFORMATION table and orders them in descending order using ORDER BY PRICE DESC but it doesn't directly give the highest price.

Table SALES

Which SQL statement lets you find the sales amount for each store?

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES;
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID;
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID;

Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table. It does not group the results by store ID, so it will return a single row with the total sales amount across all stores.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table and uses an ORDER BY clause to sort the results by store ID. However, it still doesn't group the results by store_ID.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table and uses the GROUP BY clause to group the results by store ID. It calculates the total sales amount for each store ID separately. As a result, it calculates the total sales amount for each unique store ID separately, providing a breakdown of sales amounts for each store in the dataset.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID; — This statement is incorrect because the HAVING clause is used for filtering grouped data based on a condition, not for identifying unique values. Also, "UNIQUE STORE_ID" is not a valid condition in SQL.

Table SALES

Which SQL statement lets you list all stores whose total sales amount is over 5000 ?

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000;
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID;
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID;

Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000; — This statement selects the store ID and calculates the total sales amount for each store using the SUM() aggregate function. The GROUP BY STORE_ID clause ensures that the results are grouped by store ID. The HAVING SUM(SALES_AMOUNT) > 5000 condition then filters the grouped data, selecting only those stores whose total sales amount is over 5000.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000; — This option is incorrect because the HAVING clause cannot directly reference SALES_AMOUNT without an aggregate function like SUM() since SUM(SALES_AMOUNT) is used in the SELECT statement.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID; — This option is incorrect because aggregate functions like SUM(SALES_AMOUNT) cannot be used directly in the WHERE clause. The WHERE clause is used for filtering individual rows before grouping.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID; — This option is incorrect because it tries to filter individual sales amounts (SALES_AMOUNT) directly without using the SUM() aggregate function to calculate the total sales amount for each store. The GROUP BY STORE_ID clause is used for grouping after the filtering, which is not the correct approach for filtering based on the total sales amount.

Table SALES

Which SQL statement lets you find the total number of stores in the SALES table?

1. SELECT COUNT(STORE_ID) FROM SALES;
2. SELECT COUNT(DISTINCT STORE_ID) FROM SALES;
3. SELECT DISTINCT STORE_ID FROM SALES;
4. SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID;

Answer

SELECT COUNT(DISTINCT STORE_ID) FROM SALES;

1. SELECT COUNT(STORE_ID) FROM SALES; — This query uses the COUNT() aggregate function with the STORE_ID column in the SELECT statement. It counts the number of non-null values in the STORE_ID column, and this count includes duplicates.
2. SELECT COUNT(DISTINCT STORE_ID) FROM SALES; — This option uses COUNT(DISTINCT STORE_ID) to count the number of unique store IDs in the SALES table. The DISTINCT keyword ensures that only distinct (unique) values are counted, avoiding overcounting due to duplicates.
3. SELECT DISTINCT STORE_ID FROM SALES; — This option selects distinct (unique) store IDs from the SALES table but does not count or provide the total number of stores.
4. SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID; — This option attempts to count the number of occurrences of each store ID by using COUNT(STORE_ID) and grouping by store ID with GROUP BY STORE_ID. However, this results in a count for each unique store ID separately, not the total number of stores in the table.

Table SALES

Which SQL statement allows you to find the total sales amount for Store ID 25 and the total sales amount for Store ID 45?

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   GROUP BY STORE_ID HAVING STORE_ID IN (25, 45);
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45);
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
   WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID;

Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES   
WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;

1. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID; — This query uses the IN operator to filter rows where the STORE_ID is either 25 or 45. It then calculates the total sales amount for each store ID using SUM(SALES_AMOUNT) and groups the results by STORE_ID. This query correctly finds the total sales amount for Store ID 25 and Store ID 45 separately.
2. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING STORE_ID IN (25, 45); — This query will also give the required output but it is inefficient because it first retrieves all rows from the "SALES" table, then groups the results by store ID, and finally filters the result set to include only store IDs 25 and 45. The inefficiency arises from the fact that it processes all rows in the "SALES" table before filtering out the unnecessary data. This means that it processes more data than necessary, which can be wasteful in terms of computational resources and time. A more efficient approach would be to select only the rows having store IDs 25 and 45 first (using WHERE clause), and then perform the aggregation.
3. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45); — This query filters rows where the STORE_ID is either 25 or 45 and calculates the total sales amount for these store IDs using SUM(SALES_AMOUNT). However, it doesn't include a GROUP BY clause, so it would return a single row with the total sales amount for both Store ID 25 and Store ID 45 combined.
4. SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID; — This query filter rows where the STORE_ID is both 25 and 45 simultaneously using STORE_ID = 25 AND STORE_ID = 45. However, this condition is impossible to satisfy because a single value cannot be both 25 and 45 at the same time. Therefore, this query would not return any results.

Table EXAM_RESULTS

What SQL statement do we use to find the average exam score for EXAM_ID = 1?

1. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS;
2. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1;
3. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;
4. SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1;

Answer

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;

+-----------------+
| AVG(EXAM_SCORE) |
+-----------------+
|         83.2500 |
+-----------------+

1. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS; — This statement calculates the average exam score across all exam IDs in the EXAM_RESULTS table.
2. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1; — This statement is incorrect because the WHERE clause should come before the GROUP BY clause. Additionally, grouping by EXAM_ID and then trying to filter by EXAM_ID = 1 within the GROUP BY clause will result in an error because grouping should be done before filtering.
3. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1; — This query groups the exam results by EXAM_ID and then calculates the average exam score for each group. The HAVING clause filters the groups and returns only those where the EXAM_ID is equal to 1, giving us the average exam score for the exam with EXAM_ID equal to 1.
4. SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1; — This statement calculates the count of exam scores for EXAM_ID = 1, but it doesn't calculate the average score.

Table EXAM_RESULTS

Which SQL statement do we use to find out how many students took each exam?

1. SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
2. SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
3. SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
4. SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

Answer

SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

+---------+------------------------+
| EXAM_ID | COUNT(DISTINCT STU_ID) |
+---------+------------------------+
|       1 |                      4 |
|       2 |                      5 |
+---------+------------------------+

1. SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. However, the result set does not include EXAM_ID.
2. SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and then selects the maximum STU_ID for each exam. However, this doesn't provide the count of students who took each exam, as it gives the maximum student ID instead of counting the distinct student IDs.
3. SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. The result set includes the EXAM_ID and the count of students who took each exam.
4. SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and selects the minimum STU_ID for each exam. It does not provide information about the number of students who took each exam.

Table EXAM_RESULTS

What SQL statement do we use to print out the record of all students whose last name starts with 'L'?

1. SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;
2. SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L';
3. SELECT * FROM EXAM_RESULTS WHERE LNAME 'L';
4. SELECT * FROM EXAM_RESULTS WHERE LNAME <> 'L';

Answer

SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;

+--------+-------+-------+---------+------------+
| stu_id | fname | lname | exam_id | exam_score |
+--------+-------+-------+---------+------------+
|     10 | LAURA | LYNCH |       1 |         90 |
|     10 | LAURA | LYNCH |       2 |         85 |
+--------+-------+-------+---------+------------+

1. SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%'; — The LIKE operator is used for pattern matching in SQL. '%' is a wildcard character that matches zero or more characters. 'L%' specifies that the last name (LNAME) should start with 'L' followed by zero or more characters. The SELECT * statement retrieves all columns from the EXAM_RESULTS table for the matching records.
2. SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L'; — This query attempts to select all columns (*) from the EXAM_RESULTS table where the last name (LNAME) is exactly equal to 'L'. However, when using the LIKE operator in SQL for pattern matching, we use wildcard characters (%) to represent unknown parts of a string.
3. SELECT * FROM EXAM_RESULTS WHERE LNAME 'L'; — This statement contains a syntax error. In SQL, when using the WHERE clause to filter records based on a specific condition, we need to use comparison operators or functions to define the condition properly.
4. SELECT * FROM EXAM_RESULTS WHERE LNAME <> 'L'; — This query retrieves records where the last name is not equal to 'L'. It does not specifically look for last names starting with 'L', so it's not the correct option for the given requirement.

Table EXAM_RESULTS

What is the result of the following SQL statement ?

SELECT MAX(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;

1. 90
2. 85
3. 100
4. 95

Answer

+-----------------+
| MAX(EXAM_SCORE) |
+-----------------+
|              95 |
+-----------------+

The above SQL query calculates the maximum exam score for EXAM_ID 1 from the EXAM_RESULTS table, by grouping results based on EXAM_ID and filtering using HAVING.

Given the following table :

Table : CLUB

Give the output of following SQL statements :

1. SELECT COUNT(DISTINCT SPORTS) FROM Club ;
2. SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ;
3. SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ;
4. SELECT SUM(Pay) FROM CLUB WHERE Datofapp > '1998-01-31' ;

Answer

1.

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

The SQL query SELECT COUNT(DISTINCT SPORTS) FROM Club ; calculates the count of unique values in the 'SPORTS' column of the 'Club' table. This query helps us to get information about the number of sports offered by the club.

2.

+----------+
| MIN(Age) |
+----------+
|       34 |
+----------+

The SQL query SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ; retrieves the minimum Age from the 'CLUB' table where the 'Sex' column has the value 'F'. This query gives us the age of the youngest female coach in the club.

3.

+-----------+
| AVG(Pay)  |
+-----------+
| 1100.0000 |
+-----------+

The SQL query SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ; calculates the average value of the 'Pay' column from the 'CLUB' table where the 'Sports' column has the value 'KARATE'. This query helps us to get information about the average pay of karate coaches in the club.

4.

+----------+
| SUM(Pay) |
+----------+
|     7800 |
+----------+

The SQL query SELECT SUM(Pay) FROM CLUB WHERE Dateofapp > '1998-01-31'; calculates the sum of the 'Pay' column from the 'CLUB' table where the 'Dateofapp' column has a date value greater than '1998-01-31'. This query gives us the total pay of all the coaches who joined after 31/01/1998.

Given the following table :

Table : STUDENT

Give the output of following SQL statements :

(i) SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;

(ii) SELECT SUM(Stipend) FROM Student WHERE Grade = 'B' ;

(iii) SELECT AVG(Stipend) FROM Student WHERE Class = '12A' ;

(iv) SELECT COUNT(DISTINCT) FROM Student ;

Answer

(i)

+--------------+
| MIN(AvgMark) |
+--------------+
| 64.4         |
+--------------+

(ii)

+--------------+
| SUM(Stipend) |
+--------------+
|         1150 |
+--------------+

(iii)

+--------------+
| AVG(Stipend) |
+--------------+
|          475 |
+--------------+

(iv) It will give an error because the COUNT function requires an argument specifying what to count. Additionally, the DISTINCT keyword is followed by a column name to count the distinct values of that column.

In a Database, there are two tables given below :

Table : EMPLOYEE

Table : JOB

Write SQL Queries for the following :

1. To display employee ids, names of employees, job ids with corresponding job titles.
2. To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.
3. To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in their names.
4. Identify foreign key in the table EMPLOYEE.
5. Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.

Answer

1.

SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOBID = JOB.JOBID;

+------------+-------------------+-------+--------------------------+
| EMPLOYEEID | NAME              | JOBID | JOBTITLE                 |
+------------+-------------------+-------+--------------------------+
| E1         | SUMIT SINHA       |   102 | VICE PRESIDENT           |
| E2         | VIJAY SINGH TOMAR |   101 | PRESIDENT                |
| E3         | AJAY RAJPAL       |   103 | ADMINISTARTION ASSISTANT |
| E4         | MOHIT RAMNANI     |   102 | VICE PRESIDENT           |
| E5         | SHAILJA SINGH     |   103 | ADMINISTARTION ASSISTANT |
+------------+-------------------+-------+--------------------------+

2.

SELECT EMPLOYEE.NAME, EMPLOYEE.SALES, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.SALES > 1300000;

+---------------+---------+--------------------------+
| NAME          | SALES   | JOBTITLE                 |
+---------------+---------+--------------------------+
| AJAY RAJPAL   | 1400000 | ADMINISTARTION ASSISTANT |
| SHAILJA SINGH | 1450000 | ADMINISTARTION ASSISTANT |
+---------------+---------+--------------------------+

3.

SELECT EMPLOYEE.NAME, JOB.JOBTITLE
FROM EMPLOYEE, JOB 
WHERE EMPLOYEE.JOBID = JOB.JOBID
AND EMPLOYEE.NAME LIKE '%SINGH%';

+-------------------+--------------------------+
| NAME              | JOBTITLE                 |
+-------------------+--------------------------+
| VIJAY SINGH TOMAR | PRESIDENT                |
| SHAILJA SINGH     | ADMINISTARTION ASSISTANT |
+-------------------+--------------------------+

4. In the given tables, EMPLOYEE and JOB, the JOBID column in the EMPLOYEE table is a foreign key referencing the JOBID column in the JOB table.

5.

UPDATE EMPLOYEE
SET JOBID = 104
WHERE EMPLOYEEID = 'E4';

SELECT * FROM EMPLOYEE ;

+------------+-------------------+---------+-------+
| EMPLOYEEID | NAME              | SALES   | JOBID |
+------------+-------------------+---------+-------+
| E1         | SUMIT AINHA       | 1100000 |   102 |
| E2         | VIJAY SINGH TOMAR | 1300000 |   101 |
| E3         | AJAY RAJPAL       | 1400000 |   103 |
| E4         | MOHIT RAMNANI     | 1250000 |   104 |
| E5         | SHAILJA SINGH     | 1450000 |   103 |
+------------+-------------------+---------+-------+

Show the average salary for all departments with more than 3 people for a job.

Answer

SELECT job, AVG(Sal) AS AvgSalary 
FROM Empl 
GROUP BY job HAVING COUNT(*) > 3;

+----------+-----------+
| job      | AvgSalary |
+----------+-----------+
| CLERK    |    1037.5 |
| SALESMAN |      1400 |
+----------+-----------+

Display only the jobs with maximum salary greater than or equal to 3000.

Answer

SELECT Job 
FROM Empl 
GROUP BY Job HAVING MAX(Sal) >= 3000;

+-----------+
| Job       |
+-----------+
| PRESIDENT |
| ANALYST   |
+-----------+

Find out number of employees having "Manager" as Job.

Answer

SELECT COUNT(*) AS NumManagers
FROM EmpSalary
WHERE Designation = 'Manager';

+-------------+
| NumManagers |
+-------------+
|           3 |
+-------------+

List the count of employees grouped by deptno. (table EMPL)

Answer

SELECT deptno, COUNT(*) AS employee_count
FROM EMPL
GROUP BY deptno;

+--------+----------------+
| deptno | employee_count |
+--------+----------------+
|     20 |              5 |
|     30 |              6 |
|     10 |              3 |
+--------+----------------+

List the sum of employees' salaries grouped by department. (table EMPL)

Answer

SELECT deptno, SUM(sal) AS total_salary
FROM EMPL
GROUP BY deptno;

+--------+--------------+
| deptno | total_salary |
+--------+--------------+
|     20 |        10885 |
|     30 |         9400 |
|     10 |         8750 |
+--------+--------------+

List the maximum salary of employee grouped by their department number.

Answer

SELECT deptno, MAX(sal) AS max_salary
FROM EMPL
GROUP BY deptno;

+--------+------------+
| deptno | max_salary |
+--------+------------+
|     20 |       3000 |
|     30 |       2850 |
|     10 |       5000 |
+--------+------------+

Below are the customer and order tables :

Customers

Orders

List the total of customers' orders grouped by customer (id).

Answer

SELECT c.customer_id, COUNT(o.order_id) AS total_orders
FROM Customers c, orders o
WHERE c.customer_id = o.customer_id
GROUP BY c.customer_id;

Below are the customer and order tables :

Customers

Orders

List all customers (name) who have orders (use EXISTS).

Answer

SELECT first_name, last_name
FROM Customers c
WHERE EXISTS (
    SELECT first_name, last_name
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

Below are the customer and order tables :

Customers

Orders

List the sum of the totals of orders grouped by customer and state.

Answer

SELECT c.customer_id, c.state, SUM(o.amount) AS total_order_amount
FROM Customers c, orders o
WHERE c.customer_id = o.customer_id
GROUP BY c.customer_id, c.state;

Below are the customer and order tables :

Customers

Orders

List the sum of the totals of orders where this sum is greater than $1000 grouped by customer (id) and state and ordered by state.

Answer

SELECT c.customer_id, c.state, SUM(o.amount)
FROM Customers c, orders o
WHERE c.customer_id = o.customer_id
GROUP BY c.customer_id, c.state
HAVING SUM(o.amount) > 1000
ORDER BY c.state;

Below are the customer and order tables :

Customers

Orders

List the customers (name) and their orders' details.

Answer

SELECT c.first_name, c.last_name, o.order_id, o.order_date, o.amount
FROM Customers c, Orders o
WHERE c.customer_id = o.customer_id;

Below are the customer and order tables :

Customers

Orders

List the customers (name) and the total amount of all their orders.

Answer

SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
SUM(o.amount) AS total_order_amount
FROM Customers c, Orders o 
WHERE c.customer_id = o.customer_id
GROUP BY c.customer_id;

Schemas of tables EMPL, Dept, SalaryGrade are being shown below :

EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SALARYGRADE (Lowsal, Highsal, Grade)

DEPT (Deptno, DeptName, Location)

List the department names and the number of their employees.

Answer

SELECT d.DeptName AS Department_Name, COUNT(e.EMPNO) AS Number_of_Employees
FROM DEPT d, EMPL e
WHERE d.Deptno = e.DEPTNO
GROUP BY d.DeptName;

Schemas of tables EMPL, Dept, SalaryGrade are being shown below :

EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SALARYGRADE (Lowsal, Highsal, Grade)

DEPT (Deptno, DeptName, Location)

List the employee names and the name of their departments.

Answer

SELECT e.ENAME AS Employee_Name, d.DeptName AS Department_Name
FROM EMPL e, DEPT d
WHERE e.DEPTNO = d.Deptno;

Give output for following SQL queries as per given table(s) :

Table : GARMENT

Table : FABRIC

(i) SELECT SUM(PRICE) FROM GARMENT WHERE FCODE = 'F01' ;

(ii) SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC
WHERE GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >= 1260 ;

(iii) SELECT MAX(FCODE) FROM FABRIC ;

(iv) SELECT COUNT(DISTINCT PRICE) FROM GARMENT ;

Answer

(i)

+------------+
| SUM(PRICE) |
+------------+
|       2600 |
+------------+

(ii)

+----------------+----------+
| DESCRIPTION    | TYPE     |
+----------------+----------+
| INFORMAL PANT  | COTTON   |
| INFORMAL SHIRT | COTTON   |
| FORMAL PANT    | TERELENE |
+----------------+----------+

(iii)

+------------+
| MAX(FCODE) |
+------------+
| F04        |
+------------+

(iv)

+-----------------------+
| COUNT(DISTINCT PRICE) |
+-----------------------+
|                     7 |
+-----------------------+

Give output for following SQL queries as per given table(s) :

Table : PRODUCT

Table : CLIENT

(i) SELECT DISTINCT City FROM Client ;

(ii) SELECT Manufacturer, MAX(Price), Min(Price), Count(*)
FROM Product GROUP BY Manufacturer ;

(iii) SELECT ClientName, ProductName
FROM Product, Client
WHERE Client.P_Id = Product.P_Id ;

(iv) SELECT ProductName, Price * 4 FROM Product ;

Answer

(i)

+-----------+
| City      |
+-----------+
| Delhi     |
| Mumbai    |
| Bangalore |
+-----------+

(ii)

+--------------+------------+------------+----------+
| Manufacturer | MAX(Price) | Min(Price) | Count(*) |
+--------------+------------+------------+----------+
| ABC          |         55 |         45 |        2 |
| XYZ          |        120 |         95 |        2 |
| LAK          |         40 |         40 |        1 |
+--------------+------------+------------+----------+

(iii)

+---------------+---------------+
| ClientName    | ProductName   |
+---------------+---------------+
| Cosmetic Shop | Face Wash     |
| Total Health  | Bath Soap     |
| Live Life     | Shampoo       |
| Pretty Woman  | Face Wash     |
| Dreams        | Talcum Powder |
+---------------+---------------+

(iv)

+---------------+-----------+
| ProductName   | Price * 4 |
+---------------+-----------+
| Bath Soap     |       220 |
| Face Wash     |       180 |
| Face Wash     |       380 |
| Shampoo       |       480 |
| Talcum Powder |       160 |
+---------------+-----------+

Give output for following SQL queries as per given table(s) :

Table : ITEM

Table : CUSTOMER

(i) SELECT DISTINCT City FROM Customer ;

(ii) SELECT ItemName, MAX(Price), Count(*)
FROM Item GROUP BY ItemName ;

(iii) SELECT CustomerName, Manufacturer
FROM Item, Customer
WHERE Item.I_ID = Customer.I_ID ;

(iv) SELECT ItemName, Price * 100
FROM Item WHERE Manufacturer = 'ABC' ;

Answer

(i)

+-----------+
| City      |
+-----------+
| DELHI     |
| MUMBAI    |
| BANGALORE |
+-----------+

(ii)

+-------------------+------------+----------+
| ItemName          | MAX(Price) | Count(*) |
+-------------------+------------+----------+
| LAPTOP            |      57000 |        2 |
| PERSONAL COMPUTER |      37000 |        3 |
+-------------------+------------+----------+

(iii)

+--------------+--------------+
| CustomerName | Manufacturer |
+--------------+--------------+
| N ROY        | PQR          |
| H SINGH      | XYZ          |
| R PANDEY     | COMP         |
| C SHARMA     | PQR          |
| K AGARWAL    | ABC          |
+--------------+--------------+

(iv)

+-------------------+-------------+
| ItemName          | Price * 100 |
+-------------------+-------------+
| LAPTOP            |     5500000 |
| PERSONAL COMPUTER |     3500000 |
+-------------------+-------------+

Give output for following SQL queries as per given table(s) :

Table : SENDER

Table : RECIPIENT

(i) SELECT DISTINCT SenderCity FROM Sender ;

(ii) SELECT A.SenderName, B.RecName
FROM Sender A, Recipient B
WHERE A.SenderID = B.SenderID AND B.RecCity = 'Mumbai' ;

(iii) SELECT RecName, RecAddress FROM Recipient
WHERE RecCity NOT IN('Mumbai', 'Kolkata') ;

(iv) SELECT RecID, RecName FROM Recipient
WHERE SenderID = 'MU02' OR SenderID = 'ND50' ;

Answer

(i)

+------------+
| SenderCity |
+------------+
| MUMBAI     |
| NEW DELHI  |
+------------+

(ii)

+------------+-----------+
| SenderName | RecName   |
+------------+-----------+
| R JAIN     | H SINGH   |
| S JHA      | P K SWAMY |
+------------+-----------+

(iii)

+------------+-----------------------+
| RecName    | RecAddress            |
+------------+-----------------------+
| S MAHAJAN  | 116, A VIHAR          |
| S TRIPATHI | 13, B1 D, MAYUR VIHAR |
+------------+-----------------------+

(iv)

+-------+------------+
| RecID | RecName    |
+-------+------------+
| ND08  | S MAHAJAN  |
| ND48  | S TRIPATHI |
+-------+------------+