Queries with use of Create, Update, Drop, Truncate, Insert, Delete, Alter
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan |Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Practical 1
and other statements.
1. Write a query to Create Database name piyush.
2. Write a query to Drop Database piyush.
3. Write a query to create a table
4. Write a query to Insert (inserting values in emp_piyush table).
5. Write a query to Update (update emp_piyush table).
6. Write a query to select (select emp_piyush table).
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
7. Write a query to Alter.
8. Write a query to Delete (Delete data from table).
9. Write a query to Truncate table.
[Link] a query to Alter.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Practical 2
Write a queries To Perform SQL Queries by using Between, NOT, In, NULL, Like.
1. Write a query using Between
2. Write a query usingIn operator.
3. Write a query usingNot In
4. Write a query usingLIKE operator
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
5. Write a query to useNull.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Practical 3
[Link] a queries To Perform SQL Queries by using Distinct, Count ,Order By, Group by.
(table cust_piyush)
[Link] a query to use distinct
3. Write a query using count which returns the number of rows that matches a specified
criterion.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
[Link] a query using ORDDER BY which is used to sort the result-set in ascending or
descending order.
[Link] a query using ORDDER BY to sort result in ascending order.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
[Link] a query using GROUP BY which groups the rows that have the same values into
summary rows
[Link] a query using GROUP BY with HAVING clause
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Practical 4
Table: pubisher_piyush
Table:author_piyush
Table:book_piyush
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Table:wrote_piyush
1. Find the average price for books of type “SUS”.
2. List the name of every publisher not located in New Year state.
3. List the book code and title of every book whose type is LANG or SYNC. Use the IN
operator in your command
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
4. Determine the name of most expensive, and most cheap book.
5. For every book, list the book code, book title, publisher code, and publisher name.
6. Find the book title for every book written by author no. 01. Use the IN operator in
your formation.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
DBMS PRACTICAL 5
Create a database for employee management system by implementing various
constraints.
1.) Primary Key
2.) Foreign Key
3.) Check
4.) Unique
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
5.) Not Null
6.) Default
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
DBMS PRACTICAL 6
To create the view, execute and verify the various operations as view.
Existing table:emp_piyush
1.) Creation of view
2.) Insertion of values in view
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
3.) Update statement
4.) Drop a view
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan|Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
INDEX
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Prac cal 7
To execute and verify the SQL commands for various join opera on.
Table: emp_pranjalmali
Table: dept_pranjalmali
Table: area_pranjalmali
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
1. Equi Join:
Query 1:
Query 2:
Query 3:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Query 4:
Query 5:
Query 6:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
[Link] Join:
Query 1:
Query 2:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
[Link] Product:
Query:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
[Link] Join:
Query:
[Link] Join:
Query:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
[Link] Join:
Query:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Default:
[Link] Join
a) Right Outer Join
Query:
b) Le Outer Join
Query 1:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Query 2:
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Prac cal 8
To execute and verify the SQL queries for following tables.
Table: Supplier_pranjal
Table: Parts_pranjal
Table: Catalog_pranjal
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
1) Find the names of suppliers who supply some red part.
2) Find the sids of suppliers who supply some red or black part.
3) Find the of suppliers who supply some red or are at 221, Packer Street.
4) Find the sids of suppliers who supply some red part and some brown
part.
5) Find the sids of suppliers who supply every part.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
6) Find the sids of suppliers who supply red part.
7) Find the sids of suppliers who supply red part or black part.
8) Find the sids of suppliers who supply evert red part or every black part.
9) Find the pairs of sids such that the suppliers with the first sid charges
more for some part than supplier with the second sid.
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
Prac cal 9
Aim: Write queries to implement triggers in DBMS.
Name- Farhan Khan| Class- BTech-CSE (IV Sem) | Subject- DBMS Lab
INSTITUTE OF ENGINEERING AND TECHNOLOGY
Mohanlal Sukhadia University, Udaipur