Structured Query Language (SQL) – II Computer Science - Structured Query Language (SQL) – II

The NOT NULL constraint enforces a column to not accept empty values.

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true.

How many values can be returned from a given stored function?

The string function that returns the index of the first occurrence of substring is _____________

If emp_id contain the following set {9, 7, 6, 4, 3, 1, 2}, what will be the output on execution of the given query?

Which clause is used with an "aggregate functions"?

Write the output of the following statements:

Choose the correct answer for the following questions on the basis of given table:

Table: Persons

FirstName	LastName
Naveen	Gupta
Ram	Sharma
Mohan	Kumar
Aayu	Chugh

(a) With SQL, how do you select a column named "FirstName "from a table named "Persons "?

(b) With SQL, how do we select all the columns from a table named "Persons "?

(c) With SQL, how do we select all the records from a table named "Persons "where the value of the column "FirstName "is "Naveen "?

(d) With SQL, how do we select all the records from a table named "Persons "where the value of the column "FirstName "starts with an "a "?

(e) With SQL, how do we select all the records from a table named "Persons "where the "FirstName "is "Aayu "and the "LastName "is "Chugh "?

(f) With SQL, how do we select all the records from a table named "Persons "where the "LastName "is alphabetically between (and including) "Sharma "and "Chugh "?

(g) With SQL, how can we return all the records from a table named "Persons "sorted descending by "FirstName "?

(h) With SQL, how can we insert a new record into the "Persons "table?

(i) With SQL, how can we insert "Mehta "as the "LastName "in the "Persons "table?

(j) How can we change "Chugh "into "Bajaj "in the "LastName "column in the Persons table?

(k) With SQL, how can you delete the records where the "FirstName "is "Gupta "in the Persons Table?

(l) With SQL, how can you return the number of records in the "Persons "table?

Question write SQL statements for the following.

(i) To print the current date and time.

(ii) To print the today ’s date only.

(iii) To print the month name from the date "2019-10-10 ".

(iv) To the month from the date "2020-01-10 "

(v) To print the square of 5.

(vi) To round of the number 345.56 upto 1 decimal place.

(vii) To print the modulus of 4 to 5.

(viii) To round of the number 4567.10 multiple of 10.

Assuming the given text "Informatics Practices Class-XII ", write the SQL statements to do the following:

(i) To print the length of the above string.

(ii) To extract "mat "from the string.

(iii) To print extract "xii "and print it in upper case.

(iv) To print above complete string in upper case.

(v) To print "info "in lower case.

(vi) To check whether "ces "is available in the above string or not.

(vii) To remove the leading and trailing spaces.

Write the output of the following statements:

Write the SQL statements for the following:

(a) Extract the "net "from the word "Internet ".

(b) Print the month of your date of birth.

What do you mean by join? Explain the concept of join.

Write the difference single row and multiple row function.

What do you mean by Referential Integrity?

Write the difference between WHERE and HAVING clause.

Write the output that the following statements will produce:

Write SQL statements to do the following:

(a) Using the three separate words "We,""study,"and "MySQL,"produce the following output: "We study MySQL "

(b) Use the string "Internet is a boon "and extract the string "net ".

(c) Display the length of the string "Informatics Practices ".

(d) Display the position of "My "in "Enjoying MySQL ".

(e) Display the name of current month.

Write the output produced by the following SQL commands:

Answer the following questions.

Table: Employee

No	Name	Salary	Zone	Age	Grade	Dept
1	Mukul	30000	West	28	A	10
2	Kritika	35000	Centre	30	A	10
3	Naveen	32000	West	40		20
4	Uday	38000	North	38	C	30
5	Nupur	32000	East	26		20
6	Moksh	37000	South	28	B	10
7	Shelly	36000	North	26	A	30

Table: Department

No	DName	MinSal	MaxSal	HOD
10	Sales	25000	32000	1
20	Finance	30000	50000	5
30	Admin	25000	40000	7

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

(i) Display the details of all the employees who work in Sales department.

(ii) Display the Salary, Zone, and Grade of all the employees whose HOD is Nupur.

(iii) Display the Name and Department Name of all the employees.

(iv) Display the names of all the employees whose salary is not within the specified range for the corresponding department.

(v) Display the name of the department and the name of the corresponding HOD for all the departments.

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

Answer the following questions.

Vehicle

Challan

Offence

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

(i) Display the dates of first registration and last registration from the table Vehicle.

(ii) Display the number of challans issued on each date.

(iii) Display the total number of challans issued for each offence.

(iv) Display the total number of vehicles for which the 3rd and 4th characters of RegNo are '6C'.

(v) Display the total value of challans issued for which the Off_Desc is 'Driving without License'.

(vi) Display details of the challans issued on '2010-04-03' along with Off_Desc for each challan.

(vii) Display the RegNo of all vehicles which have been challaned more than once.

(viii) Display details of each challan alongwith vehicle details, Off_desc, and Challan_Amt.

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

(c) Should any of these tables have some more column(s)? Think, discuss in peer groups, and discuss with your teacher.

Answer the following questions.

Bills

Bill No	Date	I_Code	qty
1 1 2 2 2 3 4 4 5 5 5	2010-04-01 2010-04-01 2010-04-01 2010-04-01 2010-04-01 2010-04-02 2010-04-02 2010-04-02 2010-04-03 2010-04-03 2010-04-03	1002 3001 1001 1002 2003 2002 2002 2003 2003 3001 3002	2 1 3 1 2 1 4 2 2 1 3

Items

I_Code	Name	Category
1001 1002 1003 2001 2002 2003 3001 3002	2010-04-01 2010-04-01 2010-04-01 2010-04-01 2010-04-01 2010-04-02 2010-04-02 2010-04-02	1002 3001 1001 1002 2003 2002 2002 2003

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

(i) Display the average rate of a South Indian item.

(ii) Display the number of items in each category.

(iii) Display the total quantity sold for each item.

(iv) Display total quantity of each item sold but don't display this data for the items whose total quantity sold is less than 3.

(v) Display the details of bill records along with Name of each corresponding item.

(v) Display the details of the bill records for which the item is 'Dosa'.

(vi) Display the details of the bill records for which the item is 'Dosa'.

(vii) Display the bill records for each Italian item sold.

(viii) Display the total value of items sold for each bill.

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

(c) Answer with justification (Think independently. More than one answers may be correct. It all depends on your logical thinking):

(i) Is it easy to remember the Category of item with a given item code? Do you find any kind of pattern in the items code? What could be the item code of another South Indian item?

(ii) What can be the possible uses of Bills table? Can it be used for some analysis purpose?

(iii) Do you find any columns in these tables which can be NULL? Is there any column which must not be NULL?

Consider the table "Grocer "and write SQL queries for the tasks that follow:

Table: Charity

Item_Id	ItemName	UnitPrice	Quantity (kg)	Date_Purchase
1	Rice	52.50	80	2010-02-01
2	Wheat	25.40	50	2010-03-09
3	Corn	50.80	100	2010-03-11
4	Semolina	28.90	50	2010-01-15

(Unit Price is per kg. price)

(i) Display Item name, unit price along with Date of purchase for all the Items.

(ii) Display Item name along with Month (in number) when it was purchased for all the items.

(iii) Display Item name along with year in which it was purchased for all the items.

(iv) Display Item Id, Date of Purchase and day name of week (e.g. Monday) on which it was purchased for all the items.

(v) Display names of all the items that were purchased on Mondays or Tuesdays.

(vi) Display the day name of the week on which Rice was purchased.

(vii) Display the Item name and unit price truncated to integer value (no decimal digits) of all the items.

(viii) Display current date.

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 output for the following commands:

Table- Loan_accounts

account	cust_name	loan_amount	installment	int_rate	start_rate	interest
1	R.K. Gupta	300000	36	12.00	2009-07-19	Null
2	S.P. Sharma	500000	48	10.00	2008-03-22	Null
3	K.P. Jain	300000	36	Null	2007-08-03	Null
4	M.P. Yadav	800000	60	10.00	2008-12-06	Null
5	S.P. Sinha	200000	36	12.50	2010-01-03	Null
6	P. Sharma	700000	60	12.50	2008-06-05	Null
7	K.S. Dhall	500000	48	Null	2008-03-05	Null

(a)

(b)

(c)

(d)

(e)

Create the following table named "Charity "and write SQL queries for the tasks that follow:

Table: Charity

P_Id	LastName	FirstName	Address	City	Contribution
1	Bindra	Jaspreet	5B, Gomti Nagar	Lucknow	3500.50
2	Rana	Monica	21 A, Bandra	Mumbai	2768.00
3	Singh	Jatinder	8, Punjabi Bagh	Delhi	2000.50
4	Arora	Satinder	K/1, Shere Punjab Colony	Mumbai	1900.00
5	Krishnan	Vineeta	A-75, Adarsh Nagar

(i) Display all first names in lowercase.

(ii) Display all last names of people of Mumbai city in uppercase.

(iii) Display Person Id along with First 3 characters of his/her name.

(iv) Display first name concatenated with last name for all the employees.

(v) Display length of address along with Person Id.

(vi) Display last 2 characters of City and Person ID.

(vii) Display Last Names and First names of people who have "at "in the second or third position in their first names.

(viii) Display the position of 'a' in Last name in every row.

(ix) Display Last Name and First name of people who have "a "as the last character in their First names.

(x) Display the first name and last name concatenated after removing the leading and trailing blanks.

(xi) Display Person Id, last names and contribution rounded to the nearest rupee of all the persons.

(xii) Display Person Id, last name and contribution with decimal digits truncated of all the persons.

(xiii) Display Last name, contribution and a third column which has contribution divided by 10. Round it to two decimal points.

Write the output for the following commands:

Table- Loan_accounts

account	cust_name	loan_amount	installment	int_rate	start_rate	interest
1	R.K. Gupta	300000	36	12.00	2009-07-19	Null
2	S.P. Sharma	500000	48	10.00	2008-03-22	Null
3	K.P. Jain	300000	36	Null	2007-08-03	Null
4	M.P. Yadav	800000	60	10.00	2008-12-06	Null
5	S.P. Sinha	200000	36	12.50	2010-01-03	Null
6	P. Sharma	700000	60	12.50	2008-06-05	Null
7	K.S. Dhall	500000	48	Null	2008-03-05	Null

(a)

(b)

(c)

(d)

(e)