Advertisements
Advertisements
Question
Write the outputs of the SQL queries based on the relations Teacher and Placement given below:
Table: Teacher
| T_ID | Name | Age | Department | Date_of_join | Salary | Gender |
| 1 | Arunan | 34 | Computer Sc | 2019-01-10 | 12000 | M |
| 2 | Saman | 31 | History | 2017-03-24 | 20000 | F |
| 3 | Randeep | 32 | Mathematics | 2020-12-12 | 30000 | M |
| 4 | Samira | 35 | History | 2018-07-01 | 40000 | F |
| 5 | Raman | 42 | Mathematics | 2021-09-05 | 25000 | M |
| 6 | Shyam | 50 | History | 2019-06-27 | 30000 | M |
| 7 | Shiv | 44 | Computer Sc | 2019-02-25 | 21000 | M |
| 8 | Shalakha | 33 | Mathematics | 2018-07-31 | 20000 | F |
Table: Placement
| P_ID | Department | Place |
| 1 | History | Ahmedabad |
| 2 | Mathematics | Jaipur |
| 3 | Computer Sc | Nagpur |
- SELECT Department, avg(salary) FROM Teacher GROUP BY Department;
- SELECT MAX(Date_of_Join), MIN(Date_of_Join) FROM Teacher;
- SELECT Name, Salary, T.Department, Place FROM Teacher T, Placement P WHERE T.Department = P.Department AND Salary>20000;
- SELECT Name, Place FROM Teacher T, Placement P WHERE Gender = ’F’ AND T.Department = P.Department;
Advertisements
Solution
- SELECT Department, avg(salary) FROM Teacher GROUP BY Department;
Department Avg(Salary) Computer Sc 16500.00 History 30000.00 Mathematics 25000.00 - SELECT MAX(Date_of_Join), MIN(Date_of_Join) FROM Teacher;
Max(Date_of_Join) Min(Date_of_Join) 2021-09-05 2017-03-04 - SELECT Name, Salary, T.Department, Place FROM Teacher T, Placement P WHERE T.Department = P.Department AND Salary>20000;
Name Salary Department Place Randeep 30000 Mathematics Jaipur Samira 40000 History Ahmedabad Raman 25000 Mathematics Jaipur Shyam 30000 History Ahmedabad Shiv 21000 Computer Sc Nagpur - SELECT Name, Place FROM Teacher T, Placement P WHERE Gender = 'F' AND T.Department = P.Department;
Name Place Samira Ahmedabad Suman Ahmedabad Shalaka Jaipur
APPEARS IN
RELATED QUESTIONS
______ is used to get a specified day of the month for a given date.
Which of the following belongs to an "aggregate function"?
Consider the table STUDENT given below:
| RollNo | Name | Class | DOB | Gender | City | Marks |
| 1 | Anand | XI | 6/6/97 | M | Agra | 430 |
| 2 | Chetan | XII | 7/5/94 | M | Mumbai | 460 |
| 3 | Geet | XI | 6/5/97 | F | Agra | 470 |
| 4 | Preeti | XII | 8/8/95 | F | Mumbai | 492 |
| 5 | Saniyal | XII | 8/10/95 | M | Delhi | 360 |
| 6 | Maakhiy | XI | 12/12/94 | F | Dubai | 256 |
| 7 | Neha | X | 8/12/95 | F | Moscow | 324 |
| 8 | Nishant | X | 12/6/95 | M | Moscow | 429 |
State the command to display the average marks scored by students of each gender who are in class XI?
(i) Select Gender, avg(Marks) from STUDENT where Class= "XI" group by Gender;
(ii) Select Gender, avg(Marks) from STUDENT group by Gender where Class="XI";
(iii) Select Gender, avg(Marks) group by Gender from STUDENT having Class="XI";
(iv) Select Gender, avg(Marks) from STUDENT group by Gender having Class = "XI";
Choose the correct option:
Which of the following is not an aggregate function?
Which command is used to delete a table? (in SQL)
What will be returned by the given query? select mid("Computer", 2, 4) = ______.
What will be returned by the given query? select substr("Computer", 4) = ______.
Name a function of MySQL which is used to remove trailing and leading spaces from a string.
"COUNT" keyword belongs to which categories in Mysql?
The ______ function returns m raised to the nth power.
Which of the following is not a date function?
Which function will be used to remove only the trailing spaces from a string?
What is the general format of UPPER() return type?
Site any two differences between Single Row Functions and Aggregate Functions.
Write the name of the functions to perform the following operation:
To display your name in capital letters.
Write the output produced by the following SQL statement:
SELECT YEAR(“1979/11/26”),
MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
Write the output produced by the following SQL statement:
SELECT LEFT("INDIA", 3), RIGHT("Computer Science", 4), MID("Informatics", 3, 4), SUBSTR("Practices", 3);
Which type of values will not be considered by SQL while executing the following statement?
SELECT COUNT(column name) FROM inventory;
If column “Fees” contains the data set (5000,8000,7500,5000,8000), what will be the output after the execution of the given query?
SELECT SUM (DISTINCT Fees) FROM student;
Which one of the following functions are used to find the largest value from the given data in MySQL?
Based on the table STUDENT given here, write suitable SQL queries for the following:
| Roll No | Name | Class | Gender | City | Marks |
| 1 | Abhishek | XI | M | Agra | 430 |
| 2 | Prateek | XII | M | Mumbai | 440 |
| 3 | Sneha | XI | F | Agra | 470 |
| 4 | Nancy | XII | F | Mumbai | 492 |
| 5 | Himanshu | XII | M | Delhi | 360 |
| 6 | Anchal | XI | F | Dubai | 256 |
| 7 | Mehar | X | F | Moscow | 324 |
| 8 | Nishant | X | M | Moscow | 429 |
- Display gender-wise highest marks.
- Display city-wise lowest marks.
- Display the total number of male and female students.
Write suitable SQL query for the following:
Display 7 characters extracted from the 7th left character onwards from the string ‘INDIA SHINING’.
Explain the following SQL function using a suitable example.
UCASE()
Shreya, a database administrator has designed a database for a clothing shop. Help her by writing answers to the following questions based on the given table:
| TABLE: CLOTH | |||||
| CCODE | CNAME | SIZE | COLOR | PRICE | DOP |
| C001 | JEANS | XL | BLUE | 990 | 2022-01-21 |
| C002 | T-SHIRT | M | RED | 599 | 2021-12-12 |
| C003 | TROUSER | M | GREY | 399 | 2021-11-10 |
| C004 | SAREE | FREE | GREEN | 1299 | 2019-11-12 |
| C005 | KURTI | L | WHITE | 399 | 2021-12-07 |
- Write a query to display cloth names in lowercase.
- Write a query to display the lowest price of the clothes.
- Write a query to count the total number of clothes purchased in medium size.
OR
Write a query to count the year-wise total number of clothes purchased.
