Structured Query Language Sumita arora IP Class 11 Solution

Structured Query Language Sumita arora IP Solution || Structured Query Language Sumita arora IP Class 11 Solution || Structured Query Language IP || Structured Query Language Information Practices || Structured Query Language Sumita arora Information Practices Solution || Structured Query Language Sumita arora Information Practices Class 11 Solution || Structured Query Language Sumita arora Solution

 

 

Q 1. Create the table Department table based on the following table instance chart. 

 

 

Q 2. Populate the table Department with data from table dept. Including only required columns. 

 

 

Q 3. Create the table Employee based un the following table instance chart.

 

 

 

Q4. Create table Customer as per following Table Instance Chart. 

 

 

 

Q6. Create a table Accounts to hold data as:
AccNo, Holdername, address, accounttype, amountpaid, amt_outstanding

Q7. Insert some data records in table Accounts.



Q8. Insert all those records of table Accounts into table Pending where amt_outstanding is more than 10000.

 

 

Q9. Write a query to display EName and Sal of employees whose salary is greater than or equal to 2200 from table Empl.

Q10. Write a query to display details of employees who are not getting commission from table Empl.

Q11. Write a query to display employee name and salary of those employee who don't have there salary in the range of 2500 to 4000.

Q12. Write a query to display the name, job title and salary of employee who do not have manager.

Q13. Write a query to display the name of employee whose name contains 'A' as third alphabet.

Q14. Write a query to display the name of employee whose name contains T as the last alphabet.

Q15.  Write a query to display the name of employee whose name contains 'M' as first alphabet 'L' as third alphabet.

Q16.  Write a query on the customers table whose output will exclude all customers with a rating <= 100, unless they are located in Shimla.

Q17. Write a query that selects all orders (Order table) except those with zeros or NULLS in the amt field.

Q18. Write SQL commands for the following on the basis of given table STUDENT:

1=  Display the names of the students who are getting a grade 'C' in either GAME or SUPW.

2 =Display the different games offered in the school.

3 = Display the SUPW taken up by the students, whose name starts with 'A'.

Q19. Write SQL commands for the following on the basis of given table SPORTS:

1 = Display the names of the students who have grade 'C’ in either Game1 or Game2 or both.

2 = Display the names of the students who have same game for both Game1 and Game2.

3 = Display the games taken up by the students, whose name starts with 'A'.

Q20. Write SQL commands for the following on the basis of given table CLUB :

(a) To show all information about the swimming coaches in the club.

(b) To list names of all coaches with their date of appointment (DATOFAPP) in descending order.

(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.

Q21. Write SQL commands for the following on the basis of given table STUDENT1 :

(a) Select all the Nonmedical stream students from STUDENT1.

(b) List the names of those students who are in class 12 sorted by Stipend.

(c) List all students sorted by AvgMark in descending order.

(d) Display a report, listing Name, Stipend, Stream and amount of stipend received in a year assuming that the Stipend is paid every month.

Q22. Write SQL commands for the following on the basis of given table :

(a) Select all the PROG type published by BPB from Library.

(b) Display a list of all books with Price more then 130 and sorted by Qty.

(c) Display all the books sorted by Price in ascending order.

Q23. Write SQL commands for the following on the basis of given table MOV :

(a) Display a list of all movies with Price over 20 and sorted by Price.

(b) Display all the movies sorted by QTY in decreasing order.

(c) Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as QTY * Price * 1.15 .

Q24. Write SQL commands for the following on the basis of given table GRADUATE :

(a) List the names of those students who have obtained Rank 1 sorted by NAME.

(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year assuming that the STIPEND is paid every month.

Q25. Write SQL commands for the following on the basis of given table relation Teacher:

(a) To show all information about the teacher of history department .

(b) To list the names of female teachers who are in Hindi department .

(c) To list names of all teachers with their date of joining in ascending order.

 

 

Q26.  Increase salary of employee records by 10% (table employee). 

 

 

Q27. Give commission of Rs. 500 to all employees who joined in year 1982 (table Empl). 

 

 

Q28. Allocate the department situated in BOSTON to employee with employee number 7500 (tables EMPL, Dept)

 

 

Q29. Given the following tables:

Orders (OrdNo, Ord_date, ProdNo#, Qty)

Product (ProdNo, Descp, Price)

Payment (OrdNo, Pment)

Write a query to delete all those records from table Orders whose complete payment has been made. 

 

 

Q30. Enlist the names of all tables created by you. 

 

Q31. Write Query statements for following transaction : (Consider tables of question 12)

(1)= Increase price of all products by 10%.

(2) = List the details of all orders whose payment is pending as per increased price.

(3) = Decrease prices by 10% for all those products for which orders were placed 10 months before. 

 

Q32. Modify table Empl, add another column called Grade of VARCHAR type, size 1 into it. 

 

Q33. In the added column Grade, assign grades as follows if sal is in range 700 -1500, Grade is 1 if sal is in range 1500 - 2200, Grade is 2 if sal is in range 2200 -3000, Grade is 3 if sal is in range 3000 - Grade is 4 

 

Q34.  Add a constraint (NN-Grade) in table Empl that declares column Grade not null. 

 

Q35. Insert a record of your choice in table Empl. Make sure not to enter Grade.

 

Q36. Modify the definition of column Grade. Increase its size to 2. 

 

Q37. Drop the table Empl. 

Q38. Drop table Employee and Department.

 

Q39. Add one column Email of data type VARCHAR and size 30 to the table Customer.

 

Q40. Add one more column CustomerincomeGroup of datatype VARCHAR(10)

 

Q41. Drop the column CustomerlncomeGroup from table Customer.