Queris set DDL Commands, Based on two tables, Group by order by

 

Queries Set 3 (DDL Commands)

Suppose your school management has decided to conduct cricket matches between students of Class XI and Class XII. Students of each class are asked to join any one of the four teams – Team Tehlka, Team Toofan, Team Aandhi and Team Shailab. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

a)  Create a database “Sports”.

b)  Create a table “TEAM” with following considerations:

i)   It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.

ii)    Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.

iii)   Using table level constraint, make TeamID as the primary key.

c)   Show the structure of the table TEAM using a SQL statement.

d)  As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:

a.  Row 1: (1, Tehlka)

b.  Row 2: (2, Toofan)

c.  Row 3: (3, Aandhi)

d.  Row 3: (4, Shailab)

e)   Show the contents of the table TEAM using a DML statement.

 

 

f)   Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.

 

 

Queries set 4 (Based on Two Tables)

Write following queries:

a)   Display the matchid, firstteamid, teamname, firstteamscore who scored more than 70 in first inning along with team name.

b)  Display firstteamid, teamname.

c)   Display matchid, teamname and secondteamscore between 100 to 160.

d)  Display unique team names from firstteamid.

e)   Display matchid and matchdate played by Anadhi and Shailab.

Queries Set 5 (Group by, Order By)

Consider the following table and write the queries:

 

 

a)  Display all the items in the ascending order of stockdate.

 

 

b)  Display maximum price of items for each dealer individually as per dcode from stock.

c)   Display all the items in descending orders of itemnames.

 

 

d)  Display average price of items for each dealer individually as per dcode from stock which average price is more than 5.

 

 

e)   Display the sum of quantity for each dcode.