Download App

Database Query Using–SQL — Computer Science STD 12 Science — Question

CBSE BoardEnglish MediumSTD 12 ScienceComputer ScienceDatabase Query Using–SQL4 Marks
Question

Write the SQL query questions from (i) to (iv) and write the output of SQL command for questions from (v) to (vii) given below:

EMPID FIRST NAME LAST NAME ADDRESS CITY
010 George Smith 83 First Street Howard
105 Mary Jones 842 Vine Ave. Losantiville
152 Sam Tones 33 Elm St. Paris
215 Sarah Ackerman 440 U.S. 110 Upton
244 Manila Sengupta 24 Friends Street New Delhi
300 Robert Samuel 9 Fifth Cross Washington
335 Henry Williams 12 Moore Street Boston
400 Rachel Lee 121 Harrison St. New York
441 Peter Thompson 11 Red Road Paris

Table: EMPSalary

EMPID SALARY BENEFITS DESIGNATION
010 75000 15000 Manager
105 65000 15000 Manager
152 80000 25000 Director
215 75000 12500 Manager
244 50000 12000 Clerk
300 45000 10000 Clerk
355 40000 10000 Clerk
4000 32000 7500 Salesman
441 28000 7500 Salesman

Write the SQL commands for the following :

(i) To show firstname, lastname, address and city of all employees living in paris.

(ii) To display the content of Employees table in ascending order of Firstname.

(iii) To display the firstname,lastname and total salary of all managers from the tables employee and empsalary, where total salary is calculated as
salary+benefits.

(iv) To display the maximum salary among managers and clerks from the table Empsalary.

Give the Output of following SQL commands:

(v) Select firstname, salary from employees, empsalary where designation = ‘Salesman’ and Employees.

empid=Empsalary.empid;

(vi) Select count(distinct designation) from empsalary;

(vii) Select designation, sum(salary) from empsalary group by designation having count(*) >2;

✓

Answer

Get the step-by-step solution for this question inside the Vidyadip app.

Get the answer in the app

Need a full question paper?

Generate a complete, print-ready paper with questions like this in minutes — across 16+ boards, with answer keys.

Start Generating Free

Explore more

More "4 Marks Each" from Database Query Using–SQL→Database Query Using–SQL — all question types→Computer Science STD 12 Science question papers→CBSE Board STD 12 Science question papers→CBSE Board question paper generator→

Similar questions

Write a program to sort the (name, age, height) tuples by ascending order where name is string, age and height are numbers. The tuples are input by the user. The sort criteria is:
1: Sort based on name;
2: Then sort based on age;
3: Then sort by score.
The priority is that name >age >score.
If the following tuples are given as input to the
program:
ram,19,20
Shyam,15,67
Raju,13,90
Rakesh,17,78
Then, the output of the program should be:
[('Raju', '13', '90'), ('Rakesh', '17', '78'), ('Shyam', '15', '67'), ('ram', '19', '20')]

→
Suppose we want to sort the following list of 7 elements in ascending order using

Selection sort:

Write the complete steps to sort the above along with each pass explanation.

→
Answer the following questions based on dataset given below:

TNO Tname Tadd Salary
0 T01 Amit 123 Paschim Vihar 23000
1 T02 Rajesh 6/11 Ramesh Nagar 34000
2 T03 Binny 5 West Punjabhi Bagh 12000
3 T04 Charu 23Malviya Nagar 45000
4 T05 Meenakshi 19 Meera Bagh 34000

(i) To write the statement to import the required library.

(ii) To create the data frame from the above dictionary.

(iii) To print the data frame.

(iv) To print the name columns.

(v) To print the complete information of the data frame.

(vi) To print the index of data frame.

(vii) To print the various attributes of the data frame i.e Shape, Dimensions, Columns and number of records.

(viii) To print the records whose salary is greater than 5000.

(ix) To create another data frame which will store all the records of the teacher whose salary is greater than 5000.

(x) To display the Salary.

(xi) To set the index to the column 'Tname'.

(xii) To display the record of 'Amit'.

(xiii) To display the record fo 'Amit' and 'Binny'

(xiv) To display the Salary and Address of 'Amit' and 'Binny'.

(xv) To display the records of the teachers whos are earning salary more than 10000.

(xvi) To display the records of the teachers whos are earning salary less than 40000.

(xvii) To add new column Dearness Allowance with any values.

(xviii) To add new column "HRA" using .loc().

(xix) To add new column tax using assign().

(xx) To display the HRA column.

(xxi) To display the records of the Teachers who have HRA of more than 500.

(xxii) To add a new column Total Salary which is to be calculated as Salary + HRA - Tax.

(xxiii) To fill all NaN values with 5000.

(xxiv) To replace all the NaN with space.

(xxv) To Update the new colum with values as Salary + HRA + Dearness Allowance - Tax

Write the output of the given commands:

→
Consider the following table HOSPITAL.

Table: HOSPITAL

PNo Name Age Department DateofAdm Charges Sex
1 Mayank 65 Surgery 23/02/2018 600 M
2 Babita 24 ENT 01/01/2019 400 F
3 Kashish 45 Orthopaedic 19/12/2018 400 M
4 Tarun 12 Surgery 01/10/2018 600 M
5 Manisha 36 ENT 12/01/2018 400 F
6 Imran 16 ENT 24/02/2018 400 M
7 Ankita NULL Cardiology 20/08/2018 800 F
8 Zoya 45 Gynecology 22/02/2018 500 F
9 Kush 19 Cardiology 13/01/2019 800 M
10 Shalini 31 Medicine 19/02/2018 300 F

Note: PNo is the primary key in the above table.

Write SQL commands for the statements (a) to (g) on the table HOSPITAL.

(a) To show all the information about the patients of the cardiology department.

(b) To list the names of female patients who are either in the orthopaedic or surgery department.

(c) To list the name of all the patients with their date of admission in ascending order.

(d) To display the patient’s name, charges, the age for female patients only.

(e) To count the number of patients with age > 30.

(f) To display various departments.

(g) To display the number of patients in each department.

→

Answer the following questions:

Students

Adm No Name Class Sec R No. Address Phone
1271 Utkarsh Madam 12 C 1 C-32, Punjabi Bagh 4356154
1324 Naresh Sharma 10 A 1 31, Mohan Nagar 435654
1325 Md. Yusuf 10 A 2 12/12, Chand Nagar 145654
1328 Sumedha 10 B 23 59, Moti Nagar 4135654
1364 Subya Akhtar 11 B 13 12, Janak Puri Null
1434 Varuna 12 B 21 69, Rohini Null
1461 David DSouza 11 B 1 D-34, Model Town 243554. 98787665
2324 Satinder Singh 12 C 1 1/2, Gulmohar Park 143654
2328 Peter Jones 10 A 18 21/328, Vishal Enclave 24356154
2371 Mohini Mehta 11 C 12 37, Raja Garden 435654, 6765787

Sports

Adm No Game CoachName Grade
1324 Cricket Narendra A
1326 Volleball M.P. Singh A
1271 Volleball M.P. Singh B
1434 Basket Ball I. Malhotra B
1461 Cricket Narendra B
2328 Basket Ball I. Malhotra A
2371 Basket Ball I. Malhotra A
1271 Basket Ball I. Malhotra A
1434 Cricket Narendra A
2328 Cricket Narendra B
1364 Basket Ball I. Malhotra B

(a) Based on these tables write SQL statements for the following queries:

(i) Display the lowest and the highest classes from the table STUDENTS.


(ii) Display the number of students in each class from the table STUDENTS.

(iii) Display the number of students in class 10.

(iv) Display details of the students of Cricket team.

(v) Display the Admission number, name, class, section, and roll number of the students whose grade in Sports table is 'A'.

(vi) Display the name and phone number of the students of class 12 who are play some game.

(vii) Display the Number of students with each coach.

(viii) Display the names and phone numbers of the students whose grade is 'A' and whose coach is Narendra.

(b) Identify the Foreign Keys (if any) of these tables. Justify your choices.

(c) Predict the output of each of the following SQL statements, and then verify the output by actually entering these statements:

(i) SELECT class, sec, count(*) FROM students GROUP BY class, sec;

(ii) SELECT Game, COUNT(*) FROM Sports GROUP BY Game;]

(iii) SELECT Game FROM students, Sports WHERE students.admno = sports.admno AND Students.AdmNo = 1434;

→
Write the objectives of the IT Act 2000.
→
Write a function search () to search a record in a binary file according to the student number entered by the user. Also display the message “Record not found” in case record not found in the file.
→
Write algorithm for POP(Remove) operation in stack.
→
WAP to generate n lines of the following pattern on the computer screen:
→

Write a script to create an empty list and then
(a) Input the names of 10 students and store them in the list using concatenation operator
(b) Display this list of names in reverse order

→