Computer Science
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' ;
SQL Joins & Grouping
5 Likes
Answer
1.
Output
+------------------------+
| COUNT(DISTINCT SPORTS) |
+------------------------+
| 4 |
+------------------------+
Explanation
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.
Output
+----------+
| MIN(Age) |
+----------+
| 34 |
+----------+
Explanation
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.
Output
+-----------+
| AVG(Pay) |
+-----------+
| 1100.0000 |
+-----------+
Explanation
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.
Output
+----------+
| SUM(Pay) |
+----------+
| 7800 |
+----------+
Explanation
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.
Answered By
2 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 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';
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
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'.
Consider the following tables Employee and Salary. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (vii)
Table : Employee
Eid Name Depid Qualification Sec 1 Deepali Gupta 101 MCA F 2 Rajat Tyagi 101 BCA M 3 Hari Mohan 102 B.A. M 4 Harry 102 M.A. M 5 Sumit Mittal 103 B.Tech. M 6 Jyoti 101 M.Tech. F Table : Salary
Eid Basic D.A HRA Bonus 1 6000 2000 2300 200 2 2000 300 300 30 3 1000 300 300 40 4 1500 390 490 30 5 8000 900 900 80 6 10000 300 490 89 - To display the frequency of employees department wise.
- To list the names of those employees only whose name starts with 'H'
- To add a new column in salary table. The column name is Total_Sal.
- To store the corresponding values in the Total_Sal column.
- Select max(Basic) from Salary where Bonus > 40 ;
- Select count(*) from Employee group by Sex ;
- Select Distinct Depid from Employee ;