XII CS DBMS & SQL Connectivity & Networking-Worksheet

WORKSHEET – 5
XII – COMPUTER SCIENCE
Interfacing Python with MySQL

1. Define database cursor.
2. What is a result set?
3. Write the differences between fetchone() and fetchall().
4. Write the command to save the database transaction permanently.
5. Write the command to establish a connection to the MySQL database.
6. Write the significance of rowcount in MySQL Connectivity.
7. The method used to check whether the MySQL database is successfully connected is ________.
8. ____ is the module to be imported for MySQL database connectivity in python.
9. ___ method creates a cursor object from within Python.
10. ____ command is used to undo the changes made in the MySQL database from within the Python
Program.
11. Write a database connectivity program to insert a record into the table 'employee' in MySQL database
'EMPDB'. Fields of the table are below.
empno -integer, empname -varchar, salary -decimal, DOJ -date
Note the following toestablish connectivity between python and MySQL.
user -root, password - tiger,host-localhost.
The value of the fields has to be accepted from the user.
12. Write a database connectivity program to retrieve all the records who have secured more than 90 marks
from the table ‘Student' in MySQL database 'SCHOOL'. Also return the number of records retrieved by
the SQL query. Fields of the table are below.
Rollno -integer, SName -varchar, Marks -decimal, Admdate -date
Note the following to establish connectivity between python and MySQL.
user -root, password - admin,host-localhost.
Computer Networks
1. Write any 3 advantages and disadvantages of computer networking.
2. What is a host/node and a workstation?
3. Write the significance of network hardware.
4. Define a client and a server.
5. What is networking protocol?
6. Write the acronyms for the following.
ARPANET, NSFNET, TCP/IP, IANA, ICANN, DNS, kbps, Bps
7. What is bandwidth? How it could be measured in analog systems?
8. What is data transfer rate?
9. Write note on IP Address.
10. Write any 2 differences between Ipv4 and Ipv6.
11. Write any 3 differences between circuit switching and packet switching.
12. The worldwide network of computer networks is called as _____________.
13. Write the significance of TCP/IP protocol.
14. What is a communication channel? Write its 2 types.
Aggregate functions, Group By, Having, SQL Joins
1. What is the use of Group By clause in SQL?
2. Write the differences between the following.
i) Single row and multiple row functions in SQL
ii) Where and Having clause in SQL
3. List out the aggregate functions used in SQL with its significance.
4. The aggregate functions ignore the NULL values. (True/ False)
5. Write the types of SQL joins in SQL.
6. The cartesian product is also called as ______ join in SQL.
7. The degree of the new relation formed by the cartesian product of 2 tables is ___ of the number of columns
of 2 tables.
8. The cardinality of the new relation formed by the cartesian product of 2 tables is ___ of the number of
tuples of 2 tables.
9. The join in which only one of the identical columns exists is called __________.
10. The join in which columns are compared based on the equality operator is called _______.
11. ________ join in SQL returns all the rows from the left table with the matched records in the right table.
12. Aggregate functions can be used in the ____ clause and cannot be used in _____ clause of the select
command.
13. WHERE and HAVING clauses can be used interchangeably in SELECT queries. (True/False)
14. All aggregate functions except ____ ignores NULL values.
a. Count(attribute) b. count (*) c. avg d. sum
15. ____ clause cannot be used with aggregate functions.
16. ______clause is used to specify conditions with a Group By clause.
17. ____ join returns the result when no condition is specified.
18. _____ join selects all rows from both participating tables as long as there is a match between the columns.
19. How does inner join differ from outer join?
20. How does equi join differ from inner join?
21. Joins can only be created from 2 tables. (True/False)
22. Having clause can only be used if the SELECT query has a Group by clause. (True/False)
23. SUM, AVG, MIN and MAX can only be used with numeric columns. (True/False)
24. Consider the tables given below and write the SQL queries for a) to j) and write the outputs for k) to m)
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 Administrative assistant 80000
104 Accounting Manager 70000
105 Accountant 65000
a) To display the employee id’s, employee names, job id’s and their corresponding job titles.
b) To display the names of employees, sales and the corresponding job titles who have achieved sales more
than 1300000.
c) To display names and corresponding job titles of those employees who have ‘SINGH’ in their names.
d) To display the frequency of employees jobwise.
e) To display the highest sales achieved by employees for each job.
f) To display the sum of sales achieved by the employees jobwise with the total sales more than 200000.
g) To display the average sales for all the jobs with more than 1 people for a job.
h) To display the id and names of all the employees with their salary.
i) To display the job titles in the descending order of their salary.
j) To display the employee names with their salary.
k) Select E.name, E.sales, J.salary from EMPLOYEE as E INNER JOIN JOB as J ON E.jobid=J.jobid;
l) Select J.jobid, J.jobtitle, J.salary, E.sales from EMPLOYEE RIGHT JOIN JOB ON E.jobid=J.jobid;
m) Select min(sales) from EMPLOYEE where name not like ‘_ _A%’;
25. Consider the table below.
Table: STUDENT
Rollno Name Class DOB Gender City Marks
1 Nanda X 2004-06-04 M Agra 551
2 Saurabh XII 2006-12-11 M Mumbai 462
3 Sanal XI 2005-10-15 F Delhi 400
4 Trisla XII 2006-10-05 F Mumbai 450
5 Schewart XII 2006-04-02 M Delhi 369
6 Marisla XI 2004-08-18 F Dubai 250
7 Neha X 2004-06-06 F Moscow 377
8 Nishant X 2004-02-03 M Moscow 489
Write the output for the following SQL Queries.
i) Select count(*), city from STUDENT Group By city having count(*)>1;
ii) Select max(marks), min(marks) from STUDENT;
iii) Select sum(marks) from STUDENT where DOB>=’2005-01-01’;
Write the SQL queries for the following.
iv) To display the name, class and total number of students who have secured more than 400 marks, classwise.
v) To display the number of distinct values of the city column.
vi) To display the average of marks for female students classwise..
vii) To display the number of male students citywise.
viii) To display the records from the table STUDENT in alphabetical order as per the name of the student.
ix) To display the highest marks of class XII.
26. Consider the tables given below. Write the output for the following SQL queries.
i) SELECT COUNT(*) , CITY FROM COMPANY GROUP BY CITY;
ii) SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;
iii) SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;
iv) SELECT PRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE
COMPANY. CID=CUSTOMER.CID AND PRODUCTNAME=”MOBILE”;
v) SELECT DISTINCT PRODUCTNAME FROM COMPANY;
Write the SQL queries for the following.
vi) To display the customer id, name and the corresponding company where the price ranges between
50000 and 70000.
vii) To display the Cartesian product of the tables COMPANY and CUSTOMER.
viii) To display the number of companies citywise.
ix) To display the highest price of each company with more than 1 for a company.
x) To display the total quantity for the customers whose name contains starts with ‘S’ and ends with
‘L’.
27. Consider the tables below. Write the queries for 1 and 2. Write the output for 3 to 6.
i) TO DISPLAY TOTAL QUANTITY IN STORE OF UNISEX TYPE WATCHES.
ii) TO DISPLAY WATCH NAME AND THEIR QUANTITY SOLD IN FIRST QUARTER;
iii) SELECT MAX (PRICE), MIN(QTY_STORE) FROM WATCHES;
iv) SELECT QUARTER, SUM(QTY SOLD) FROM SALE GROUP BY QUARTER;
v) SELECT WATCH_NAME, PRICE, TYPE FROM WATCHES W, SALE S WHERE W.
WAT£H1D!=S.WATCHID;
vi) SELECT WATCH_NAME, QTYSTORE, SUM (QTY_SOLD), QTY_STORESUM (QTYSOLD)
“STOCK” FROM WATCHES W, SALE S WHERE W. WATCHID = S.WATCHID GROUP BY
S.WATCHID;

XII CS DBMS & SQL Connectivity & Networking-Worksheet

Uploaded by

Copyright:

Available Formats

XII CS DBMS & SQL Connectivity & Networking-Worksheet

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

XII CS DBMS & SQL Connectivity & Networking-Worksheet

Uploaded by

Copyright:

Available Formats

WORKSHEET – 5

XII – COMPUTER SCIENCE

Interfacing Python with MySQL

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

You might also like