5 Marks Each

Questions

🎯

Test yourself on this topic

4 questions · timed · auto-graded

Question 15 Marks

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

(a) (i)

(ii)

(iii)

(iv)

(v)

(b) Dept is the foreign key for these tables. This is because, Dept is used to link the two tables and it is a primary key for one of the tables.

View full question & answer→

Question 25 Marks

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.

Answer

(a) (i)

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

(viii)

(b) RegNo is the foreign key for the tables as it can be used to link each other.

View full question & answer→

Question 35 Marks

Answer the following questions.

Bills

Bill No

Date

I_Code

qty

2010-04-01

2010-04-02

2010-04-03

1002

3001

1001

1002

2003

2002

2003

3001

3002

Items

I_Code

Name

Category

1001

1002

1003

2001

2002

2003

3001

3002

2010-04-01

2010-04-02

1002

3001

1001

1002

2003

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?

Answer

(a) (i)

(ii)

(iii)

(iv)

(v)

(vi)

(vii)

(viii)

(b) I_Code is the foreign key among these tables. This is because, I_Code is used to link the two tables and it is a primary key for Items table.

(c) (i) Yes, it is easy to remember the category of item with a given item code. There is a kind of pattern in the item codes, South Indian dishes must be in the range from 1000 to 1999, Chinese dishes must be in the range of 2000 to 2999 and Italian food must be in the range of 3000 to 3999. The code of another South Indian item could be 1004.

(ii) The possible uses of BILLS table can be:

1. Maintenance of bill records.

2. Maintenance of sale records.

3. Legal proof.

Yes, it can be used for some analysis purpose also. We can find overtime sales of the items or a particular item.

(iii) No. None of the columns in the table can be NULL.

View full question & answer→

Question 45 Marks

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.

View full question & answer→

Computer Science 5 Marks Each

5 Marks Each

Test yourself on this topic