ISM LAB Practical FILE Questions 4 Dec PDF

Preview

Summary

ims...

Description

ISM LAB PRACTICAL FILE Instructions for questions 1. First set of questions as in table of content – Q 1-19 2. All questions to be given Heading1 and Table of content to be generated automatically as shown in the example 3. Standard format of File will apply. 4. All questions will have a. Syntax of command b. Mysql statements c. Screen shot of commands

TABLE OF CONTENT Contents Q1. (a) What do you mean by DBMS?...................................................................................................................4 Q1. (b) Explain the features of DBMS....................................................................................................................4 Q2. Define with examples Primary Key , Foreign key , Alternate key & Candidate Key..........................................6 Q3. What is SQL? Discuss the advantages of SQL.................................................................................................8 Q4. What is MYSQL? What are the types of Command in MYSQL like DDL, DML and DCL?..................................9 Q5. Write Steps to connect MYSQL server through XAMPP................................................................................10 Q6. Create a Database EMP . Use EMP . Create a Table STUDENT with 4 Columns – S_ID, S_name, S_email and course.............................................................................................................................................12 Q7. Insert 10 records in the table STUDENT. The first name should be your name in the table..........................12 Q8. Write a query in SQL to select only S_name, and S_email of the STUDENT. Also write a query to display names of students in course BBA...........................................................................................................13 Q9. Write a query in SQL to select only S_name in descending and ascending order ........................................13 Q10. Change the name of the student with S_ID =2 as Parth.............................................................................13 Q11. a) Add a column “Salary” and assign salary to each student. Show query to display salary in ascending order...................................................................................................................................................14 b) Increase salary of all students of BCOM by Rs 10000......................................................................................14 Q12. Write a query to find the maximum , Minimum and average salary...........................................................15 Q13. Write a query to find the name of the student having maximum salary....................................................15 Q14. Write a query to find the count of students , count of students in BBA , count of students in BCOM ............................................................................................................................................................................ 16 Q15.a) Delete the record with S_ID= 6,8,9 using a single query.........................................................................16 b) Delete all the records from the table STUDENT for students in BBA course using a single query...................16 Q16. Add column Temp decimal . Delete the column Temp from the table STUDENT........................................17 Q 17. Delete the table STUDENT.........................................................................................................................17

Q18. a) Create a table for Bank_detail with ( Acc_No integer , Acc_type char , C_name varchar(50) , balance decimal ) .Make Primary Key as their account number..........................................................................17 b) Add primary key in existing table STUDENT....................................................................................................17 Q19 .Insert the database for 10 customers in Bank_detail................................................................................18 Q20. Show the entry for Null Entry for Customer account number in table bank_detail....................................19 Q21.Count the records where balance is greater than 50000.............................................................................19 Q22. Update the table bank_detail for the balance equal to 10000 if it is less than 10000..............................19 Q23. Delete the rows from the table bank_detail where balance is between 10000 & 20000...........................19 Q24. Define the following functions with examples:...........................................................................................19 Count function....................................................................................................................................................19 Sum function.......................................................................................................................................................20 Average/AVG function.........................................................................................................................................20 Max function.......................................................................................................................................................20 Min function........................................................................................................................................................20 Q25. Explain GROUP BY Statement with example...............................................................................................20 Q 26: Find out different status from the orders table. Create this table and enter 7 records with status as CANCELLED(3) , INPROCESS(1) , SHIPPED (1) and DELIVERED(2). Order table has fields – OrderNumber , OrderDate, Status , CustomerNumber................................................................................................................22 Q 27: Show the orders recent on the top and highest value order with customer name. Add two columns QTY & Unit Price in the table................................................................................................................22 Q 28: a) Define SQL FOREIGN KEY Constraint......................................................................................................22 b) Create Table Persons ( PersonId, LastName, FirstName, Age) . Create Table Orders (OrderId, OrderNo, PersonId) with Foreign Key..................................................................................................................................22 Q29 Addition and removal of foreign Key in existing table Orders.....................................................................23 Q30 : What are joins ?Explain INNER, OUTER and FULL JOIN with example......................................................23 Q31: Practice question on primary , foreign Key & functions..............................................................................25 Create table Staff................................................................................................................................................25 STAFF_ID | FIRST_NAME | EMAIL | PHONE_NUMBER

| HIRE_DATE | JOB_ID

| SALARY.......................25

Make STAFF_ID as primary key............................................................................................................................25 Create Table Jobs.................................................................................................................................................25 JOB_ID

, TITLE , MIN SALARY , MAX SALARY , NO of JOBS................................................................................25

Create Foreign key in Staff table.........................................................................................................................25 Find name of Oldest staff in the company ( Hire date to be min)........................................................................25 Find monthly salary bill of the company.............................................................................................................25 Find name of recent staff in the company...........................................................................................................25 Find Job title of staff with Maximum salary........................................................................................................25

Sample answer format

Q10. Change the name of the student with S_ID =2 as Parth. The SQL UPDATE Statement: The UPDATE statement is used to modify the existing records in a table. UPDATE Syntax UPDATE tablS_name SET column1 = value1, column2 = value2, ... WHERE condition; Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement. The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated! STEPS:

 Mysql>update STUDENT set S_name= ‘Parth’ where S_ID=2;’  ‘Mysql>Select * from STUDENT;’ SCREEN SHOT :...