Question 1

Question 1Table BOOK_INFORMATIONColumn NameBOOK_IDBOOK_TITLEPRICEWhich SQL statement allows you to find the highest price from the table BOOK_INFORMATION?SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION;SELECT MAX(PRICE) FROM BOOK_INFORMATION;SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION;SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC;

Accepted Answer

SELECT MAX(PRICE) FROM BOOK_INFORMATION;ExplanationSELECT 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.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.SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION; — There is no MAXIMUM() function in SQL.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.

Question 2

Question 2Table SALESColumn NameSTORE_IDSALES_DATESALES_AMOUNTWhich SQL statement lets you find the sales amount for each store?SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID;

Accepted Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;ExplanationSELECT 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.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.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.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.

Question 3

Question 3Table SALESColumn NameSTORE_IDSALES_DATESALES_AMOUNTWhich SQL statement lets you list all stores whose total sales amount is over 5000 ?SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALESWHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALESWHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID;

Accepted Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;ExplanationSELECT 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.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.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.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.

Question 4

Question 4Table SALESColumn NameSTORE_IDSALES_DATESALES_AMOUNTWhich SQL statement lets you find the total number of stores in the SALES table?SELECT COUNT(STORE_ID) FROM SALES;SELECT COUNT(DISTINCT STORE_ID) FROM SALES;SELECT DISTINCT STORE_ID FROM SALES;SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID;

Accepted Answer

SELECT COUNT(DISTINCT STORE_ID) FROM SALES;ExplanationSELECT 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.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.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.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.

Question 5

Question 5Table SALESColumn NameSTORE_IDSALES_DATESALES_AMOUNTWhich SQL statement allows you to find the total sales amount for Store ID 25 and the total sales amount for Store ID 45?SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALESWHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALESGROUP BY STORE_ID HAVING STORE_ID IN (25, 45);SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45);SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALESWHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID;

Accepted Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES   
WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;ExplanationSELECT 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.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.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.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.

Question 6

Question 6Table EXAM_RESULTSSTU IDFNAMELNAMEEXAM IDEXAM_SCORE10LAURALYNCH19010LAURALYNCH28511GRACEBROWN17811GRACEBROWN27212JAYJACKSON19512JAYJACKSON29213WILLIAMBISHOP17013WILLIAMBISHOP210014CHARLESPRADA285What SQL statement do we use to find the average exam score for EXAM_ID = 1?SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS;SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1;SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1;

Accepted Answer

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;Output+-----------------+
| AVG(EXAM_SCORE) |
+-----------------+
|         83.2500 |
+-----------------+
ExplanationSELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS; — This statement calculates the average exam score across all exam IDs in the EXAM_RESULTS table.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.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.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.

Question 7

Question 7Table EXAM_RESULTSSTU IDFNAMELNAMEEXAM IDEXAM_SCORE10LAURALYNCH19010LAURALYNCH28511GRACEBROWN17811GRACEBROWN27212JAYJACKSON19512JAYJACKSON29213WILLIAMBISHOP17013WILLIAMBISHOP210014CHARLESPRADA285Which SQL statement do we use to find out how many students took each exam?SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

Accepted Answer

SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;Output+---------+------------------------+
| EXAM_ID | COUNT(DISTINCT STU_ID) |
+---------+------------------------+
|       1 |                      4 |
|       2 |                      5 |
+---------+------------------------+
ExplanationSELECT 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.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.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.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.

Question 8

Question 8Table EXAM_RESULTSSTU IDFNAMELNAMEEXAM IDEXAM_SCORE10LAURALYNCH19010LAURALYNCH28511GRACEBROWN17811GRACEBROWN27212JAYJACKSON19512JAYJACKSON29213WILLIAMBISHOP17013WILLIAMBISHOP210014CHARLESPRADA285What SQL statement do we use to print out the record of all students whose last name starts with 'L'?SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L';SELECT * FROM EXAM_RESULTS WHERE LNAME 'L';SELECT * FROM EXAM_RESULTS WHERE LNAME <> 'L';

Accepted Answer

SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;Output+--------+-------+-------+---------+------------+
| stu_id | fname | lname | exam_id | exam_score |
+--------+-------+-------+---------+------------+
|     10 | LAURA | LYNCH |       1 |         90 |
|     10 | LAURA | LYNCH |       2 |         85 |
+--------+-------+-------+---------+------------+
ExplanationSELECT * 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.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.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.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.

Question 9

Question 9Table EXAM_RESULTSSTU IDFNAMELNAMEEXAM IDEXAM_SCORE10LAURALYNCH19010LAURALYNCH28511GRACEBROWN17811GRACEBROWN27212JAYJACKSON19512JAYJACKSON29213WILLIAMBISHOP17013WILLIAMBISHOP210014CHARLESPRADA285What is the result of the following SQL statement ?SELECT MAX(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;908510095

Accepted Answer

Output+-----------------+
| MAX(EXAM_SCORE) |
+-----------------+
|              95 |
+-----------------+
ExplanationThe 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.

Question 10

Question 10Given the following table :Table : CLUBCOACH-IDCOACHNAMEAGESPORTSDATOFAPPPAYSEX1KUKREJA35KARATE27/03/19961000M2RAVINA34KARATE20/01/19981200F3KARAN34SQUASH19/02/19982000M4TARUN33BASKETBALL01/01/19981500M5ZUBIN36SWIMMING12/01/1998750M6KETAKI36SWIMMING24/02/1998800F7ANKITA39SQUASH20/02/19982200F8ZAREEN37KARATE22/02/19981100F9KUSH41SWIMMING13/01/1998900M10SHAILYA37BASKETBALL19/02/19981700MGive the output of following SQL statements :SELECT COUNT(DISTINCT SPORTS) FROM Club ;SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ;SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ;SELECT SUM(Pay) FROM CLUB WHERE Datofapp > '1998-01-31' ;

Accepted Answer

1.Output+------------------------+
| COUNT(DISTINCT SPORTS) |
+------------------------+
|                      4 |
+------------------------+
ExplanationThe 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.Output+----------+
| MIN(Age) |
+----------+
|       34 |
+----------+
ExplanationThe 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.Output+-----------+
| AVG(Pay)  |
+-----------+
| 1100.0000 |
+-----------+
ExplanationThe 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.Output+----------+
| SUM(Pay) |
+----------+
|     7800 |
+----------+
ExplanationThe 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.

Question 11

Question 11Given the following table :Table : STUDENTNo.NameStipendStreamAvgMarkGradeClass1Karan400.00Medical78.5B12B2Divakar450.00Commerce89.2A11C3Divya300.00Commerce68.6C12C4Arun350.00Humanities73.1B12C5Sabina500.00Nonmedical90.6A11A6John400.00Medical75.4B12B7Robert250.00Humanities64.4C11A8Rubina450.00Nonmedical88.5A12A9Vikas500.00Nonmedical92.0A12A10Mohan300.00Commerce67.5C12CGive 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 ;

Accepted Answer

(i)Output+--------------+
| MIN(AvgMark) |
+--------------+
| 64.4         |
+--------------+
(ii)Output+--------------+
| SUM(Stipend) |
+--------------+
|         1150 |
+--------------+
(iii)Output+--------------+
| 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.

Question 12

Question 12In a Database, there are two tables given below :Table : EMPLOYEEEMPLOYEEIDNAMESALESJOBIDE1SUMIT SINHA1100000102E2VIJAY SINGH TOMAR1300000101E3AJAY RAJPAL1400000103E4MOHIT RAMNANI1250000102E5SHAILJA SINGH1450000103Table : JOBJOBIDJOBTITLESALARY101President200000102Vice President125000103Administration Assistant80000104Accounting Manager70000105Accountant65000106Sales Manager80000Write SQL Queries for the following :To display employee ids, names of employees, job ids with corresponding job titles.To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in their names.Identify foreign key in the table EMPLOYEE.Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.

Accepted Answer

1.SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE
FROM EMPLOYEE, JOB
WHERE EMPLOYEE.JOBID = JOB.JOBID;Output+------------+-------------------+-------+--------------------------+
| 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;Output+---------------+---------+--------------------------+
| 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%';Output+-------------------+--------------------------+
| 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';OutputSELECT * 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 |
+------------+-------------------+---------+-------+

Question 13

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

Accepted Answer

SELECT job, AVG(Sal) AS AvgSalary 
FROM Empl 
GROUP BY job HAVING COUNT(*) > 3;Output+----------+-----------+
| job      | AvgSalary |
+----------+-----------+
| CLERK    |    1037.5 |
| SALESMAN |      1400 |
+----------+-----------+

Question 14

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

Accepted Answer

SELECT Job 
FROM Empl 
GROUP BY Job HAVING MAX(Sal) >= 3000;Output+-----------+
| Job       |
+-----------+
| PRESIDENT |
| ANALYST   |
+-----------+

Question 15

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

Accepted Answer

SELECT COUNT(*) AS NumManagers
FROM EmpSalary
WHERE Designation = 'Manager';Output+-------------+
| NumManagers |
+-------------+
|           3 |
+-------------+

Question 16

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

Accepted Answer

SELECT deptno, COUNT(*) AS employee_count
FROM EMPL
GROUP BY deptno;Output+--------+----------------+
| deptno | employee_count |
+--------+----------------+
|     20 |              5 |
|     30 |              6 |
|     10 |              3 |
+--------+----------------+

Question 17

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

Accepted Answer

SELECT deptno, SUM(sal) AS total_salary
FROM EMPL
GROUP BY deptno;Output+--------+--------------+
| deptno | total_salary |
+--------+--------------+
|     20 |        10885 |
|     30 |         9400 |
|     10 |         8750 |
+--------+--------------+

Question 18

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

Accepted Answer

SELECT deptno, MAX(sal) AS max_salary
FROM EMPL
GROUP BY deptno;Output+--------+------------+
| deptno | max_salary |
+--------+------------+
|     20 |       3000 |
|     30 |       2850 |
|     10 |       5000 |
+--------+------------+

Question 19

Question 19Below are the customer and order tables :Customerscustomer id (PK)first_namelast_nameemailaddresscitystatezipOrdersorder id (PK)order_dateamountcustomer_id (FK)List the total of customers' orders grouped by customer (id).

Accepted 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;

Question 20

Question 20Below are the customer and order tables :Customerscustomer id (PK)first_namelast_nameemailaddresscitystatezipOrdersorder id (PK)order_dateamountcustomer_id (FK)List all customers (name) who have orders (use EXISTS).

Accepted 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
);

Querying Using SQL