Scribd
Upload
6 views3 pages

Lab 2

The document outlines a lab assignment for the Database Management Systems course at Amrita Vishwa Vidyapeetham, detailing the structure of two tables: 'department' and 'employee'. It includes specifications for column names, data types, sizes, and constraints, as well as a series of SQL queries to create tables, insert data, and retrieve specific information. The queries cover a range of operations including data selection, filtering, and aggregation based on various conditions.

Uploaded by

niyaelsalaiji
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views3 pages

Lab 2

The document outlines a lab assignment for the Database Management Systems course at Amrita Vishwa Vidyapeetham, detailing the structure of two tables: 'department' and 'employee'. It includes specifications for column names, data types, sizes, and constraints, as well as a series of SQL queries to create tables, insert data, and retrieve specific information. The queries cover a range of operations including data selection, filtering, and aggregation based on various conditions.

Uploaded by

niyaelsalaiji
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Amrita Vishwa Vidyapeetham, Amritapuri

Department of Computer Science and Engineering


23CSE202
Database Management Systems
Lab Assignment – 2

department

Column name Datatype Size Constraint


deptno Integer PK
dname Varchar 14 Not null
loc Varchar 20

employee
Column name Datatype Size Constraint
empno Integer PK
ename Varchar 20 Not null
job Varchar 10
mgr_id Integer
hired_date date
basic_sal Numeric (6,2) Default value 1000
incentive Numeric (6,2) Should not be greater than basic_sal
deptno Integer Refers to deptno of dept table
Queries

1. Create the tables with suitable constraints.


2. Insert data in the two tables.
3. Select all data from the DEPARTMENT table.
4. Get the details of all the employees.
5. Show the details of employee ‘BLAKE’.
6. Get employee number, employee name of employees who are managers.
7. Display unique jobs with second letter as ‘a' from the EMPLOYEE table.
8. Display the names of employees concatenated with their jobs.
9. Display all the names, department numbers and hired dates from the EMPLOYEE table.
10. Display employees in the ascending order of their names.
11. Find the names of all employees that begin with ‘S’ or ‘J’
12. Get the highest salary from the EMPLOYEE table.
13. Display the names, deptno of all employees who receive salary between 10000 and 25000.
14. List department number and count of employees in each department ordered by department number.
15. List the names and hired date of managers and clerks without incentives.
16. Delete the records with deptno '10' from the EMPLOYEE table.
17. Print the names and jobs of all employees except ‘analyst’.
18. Print the name of employees whose salaries are greater than the value 21000.
19. Find the names of employees who have a salary equal to Rs 13000.
20. Display the empname, deptno, hired date information in the dept '20' and '30'.

You might also like