Computer Science
Table EXAM_RESULTS
| STU ID | FNAME | LNAME | EXAM ID | EXAM_SCORE |
|---|---|---|---|---|
| 10 | LAURA | LYNCH | 1 | 90 |
| 10 | LAURA | LYNCH | 2 | 85 |
| 11 | GRACE | BROWN | 1 | 78 |
| 11 | GRACE | BROWN | 2 | 72 |
| 12 | JAY | JACKSON | 1 | 95 |
| 12 | JAY | JACKSON | 2 | 92 |
| 13 | WILLIAM | BISHOP | 1 | 70 |
| 13 | WILLIAM | BISHOP | 2 | 100 |
| 14 | CHARLES | PRADA | 2 | 85 |
What is the result of the following SQL statement ?
SELECT MAX(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;
- 90
- 85
- 100
- 95
SQL Joins & Grouping
1 Like
Answer
Output
+-----------------+
| MAX(EXAM_SCORE) |
+-----------------+
| 95 |
+-----------------+
Explanation
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.
Answered By
3 Likes
Related Questions
Table EXAM_RESULTS
STU ID FNAME LNAME EXAM ID EXAM_SCORE 10 LAURA LYNCH 1 90 10 LAURA LYNCH 2 85 11 GRACE BROWN 1 78 11 GRACE BROWN 2 72 12 JAY JACKSON 1 95 12 JAY JACKSON 2 92 13 WILLIAM BISHOP 1 70 13 WILLIAM BISHOP 2 100 14 CHARLES PRADA 2 85 Which 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;
Table EXAM_RESULTS
STU ID FNAME LNAME EXAM ID EXAM_SCORE 10 LAURA LYNCH 1 90 10 LAURA LYNCH 2 85 11 GRACE BROWN 1 78 11 GRACE BROWN 2 72 12 JAY JACKSON 1 95 12 JAY JACKSON 2 92 13 WILLIAM BISHOP 1 70 13 WILLIAM BISHOP 2 100 14 CHARLES PRADA 2 85 What 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';
Given the following table :
Table : CLUB
COACH-ID COACHNAME AGE SPORTS DATOFAPP PAY SEX 1 KUKREJA 35 KARATE 27/03/1996 1000 M 2 RAVINA 34 KARATE 20/01/1998 1200 F 3 KARAN 34 SQUASH 19/02/1998 2000 M 4 TARUN 33 BASKETBALL 01/01/1998 1500 M 5 ZUBIN 36 SWIMMING 12/01/1998 750 M 6 KETAKI 36 SWIMMING 24/02/1998 800 F 7 ANKITA 39 SQUASH 20/02/1998 2200 F 8 ZAREEN 37 KARATE 22/02/1998 1100 F 9 KUSH 41 SWIMMING 13/01/1998 900 M 10 SHAILYA 37 BASKETBALL 19/02/1998 1700 M Give 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' ;
In a Database, there are two tables given below :
Table : EMPLOYEE
EMPLOYEEID NAME SALES JOBID E1 SUMIT SINHA 1100000 102 E2 VIJAY SINGH TOMAR 1300000 101 E3 AJAY RAJPAL 1400000 103 E4 MOHIT RAMNANI 1250000 102 E5 SHAILJA SINGH 1450000 103 Table : JOB
JOBID JOBTITLE SALARY 101 President 200000 102 Vice President 125000 103 Administration Assistant 80000 104 Accounting Manager 70000 105 Accountant 65000 106 Sales Manager 80000 Write 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'.