QB365 Student Learing Managemnet System
Std: 12th Standard CBSE - Computer Science
chapter: Structured Query Language (SQL) - Important Questions And Answers
Visit QB365-SLMS - [Link] Complete Questions and Answers
Questions:
2 Marks
[Link] is relation? Define the relational data model.
[Link] between cardinality and degree of a table with the help of an example.
[Link] the following table carefully and write the names of the most appropriate columns, which can be considered as
(i) candidate keys and (ii) primary key
Transaction
Id Product Qty Price
Date
101 Plastic Folder12" 100 3400 2014-12-14
104Pen stand standard 200 4500 2015-01-31
105Stapler Medium 250 1200 2015-02-28
109Punching Machine Big 200 1400 2015-03-12
103Stapler Mini 100 1500 2015-02-02
[Link] degree and cardinality. Based upon given table write degree and cardinality.
Patient
PatNO ParName Dept DocID
1 LEENA ENT 100
2 SUPREETH Ortho 200
3 MADHU ENT 100
4 NEHA ENT 100
5 DEEPAK Ortho 200
[Link] the following table and answer the parts (i) and (ii):
Table: store
ITEM CODE ITEMS OTY RATE
10 Gel Pen classic1150 25
11 Sharpener 1500 10
12 Ball Pen 0.5 1600 12
13 Eraser 1600 5
15 Ball Pen 0.25 800 20
(i) In the above table, can we have Qty as primary key. [Answer as Yes/No.].Justify your answer.
(ii) What is the cardinality and degree of the above table?
[Link] the most common parameters and the usage that are passed to communicate with the database.
[Link] the tables FACULTY and COURSES with structure as follows
FACULTY COURSES
F _ ID C _ ID
FName F _ID
LName Cname
Hiredate Cname
Salary Fees
Write Python codes for the following.
(a) To display details of these faculties whose salary is greater than 12000
(b) To display the details of courses whose fees is in the range of 15000 to 50,000 (both values included)
(c) To increase the fees of course "System Design" by 500
(d) To display the count of unique F_ID from courses table.
[Link] the following table structure of table Hospital.
Hospital
Hospital_ id(p)
Hospital_name
Bed_Count
Write Python code to create the above table structure
[Link] the structure of table Doctor given below
Doctor
Doctor_id(p)
Doctor _Name
Hospital_id(F)
Joining_Date
Specialty
Salary
Experience.
Write Python code to create the above table.
[Link] between Primary key and Candidate key.
[Link] needs to display name of teachers, who have "O" as the third character in their name.
She wrote the following query.
SELECT NAME FROM TEACHER WHERE NAME = "**O?";
But the query isn't producing the result. Identify the problem.
Visit QB365-SLMS - [Link]
Answers Key:
2 Marks
1. A relation is a table having atomic values, unique row and unordered rows and columns. The relational model represent data and relationship among
data by a collection of tables known as relation, each of which has a number of columns with unique names.
2. Cardinality is defined as the number of rows in a table. Degree is the number of columns in a table.
e.g. consider the following table:
Table: Account
Acno Cname
A 101Rahul
A 102Riya
A 103Darsh
Cardinality: 3
Degree : 2
3. Candidate keys: Id, Product
Primary key: Id
4. Degree is the number of attributes or columns present in a table.
Cardinality is the number of tuples or rows present in a table.
Degree: 4
Cardinality: 5
5. (i) We cannot use Qty as primary key because there is a duplication of values and primary key value cannot be duplicate.
(ii) Degree: 4
Cardinality: 5
6. Parameter Usage
Data source name. This usually includes the name of your
Dsn database
and the server where it is running.
Host Host, or network system name, on which the database runs.
Database Name of the database
User User name for connecting to the database
Password Password for the given user name
7. (a) import MySQLdb
db = [Link]('localhost' ,'FacAdmin','FacAdmin@pwd','college')
cursor=[Link]()
sql="""select* from Faculty where salary>%F""",
search_ value=(12000,)
try:
[Link](sql.search_ value)
result=cursor. fetchall()
print("ID, Name, Hiredate")
for row in result:
ID=row[0]
name-row[1]-" "row[2]
Hiredate=row[2]
print (ID,name, Hiredate)
print [Link], "Records found"
except:
print ("Error: can't Fetch records")
[Link]()
[Link]()
(b) import MySQLdb
db=MySQLdb. connect('localhost' ,'FacAdmin','FacAdmin@pwd','college')
cursor=[Link]()
sql="""select * from courses where Fees between %F and %F"""
search_value=(15000,50000)
try:
[Link](sql, search_value)
results = [Link]()
print ("CID Faculty Course Fee")
for row in results:
print (row[0],row[1], row[2], row[3])
print ([Link], "Records Found")
[Link]()
[Link]()
(c) import MySQLdb
db=[Link]('localhost' ,'FacAdmin','
FacAdmin@pwd','college')
cursor = [Link]()
sql= Update Courses set Fees = Fees+500
where Cname=%s"''''
update value = ('System Design',)
try:
[Link](sql, update_value)
[Link]()
print ([Link], "record updated")
except
db. rollback.
[Link]()
[Link]()
(d) import MySQLdb
db = [Link]('localhost' ,'FacAdmin',"FacAdmin@pwd',' college')
cursor=[Link]()
sql="""select count (Distinct F_ID) from Courses"""
try:
[Link](sql)
print (row[0], "unique F_ID exist")
except:
print ("Error could not Fetch records")
[Link]()
[Link]()
8. import MySQLdb
db = [Link]('localhost' ,'FacAdmin','FacAdmiil@123','XYZHOS')
[Link]()
[Link]("DROP TABLE IF EXISTS Hospital")
sql="""Create Table Hospital('Hospital _ id',
INT NOTNULL, 'Hospital_name' CHAR(50)
NOTNUL
'Bed_Count' INT, PRIMARY KEY ('Hospital_id')
)"""cursor.~xecute(sql)
[Link]()
[Link]()
9. import MySQLdb
db = [Link]('localhost' ,'HosAdmin','HosAdmin@123' ,'XYZHospital')
cursor=[Link]()
[Link]("DROP TABLE IF EXISTS DOCTORS")
sql="""Create Table Doctors (,DoctocId' INT NOTNULL, 'Doctor_Name' Char(50) NOTNULL, 'HospitalId' INT NOTNULL, 'Joining Date' Date NOTNULL,
'Speciality' char(50), 'Salary'
Float' 'Experience' Float PrimaryKey
(Doctor_ Id,))"""
[Link](sql)
[Link]()
[Link]()
10. A Candidate Key can be any column or a combination of columns that can qualify as unique Key in database. There can be multiple Candidate keys
in one table. Where as A Primary Key is a column or a combination of columns that uniquely identify a record. There can be only one primary key in
one table
Degree: It is the total number of attributes (number of columns) in the table.
Cardinality: It is the total number of tuples (number of rows) in the table.
11. The wildcards are incorrect. The corrected query is SELECT NAME FROM TEACHER WHERE NAME LIKE' _ _ 0%';