[go: up one dir, main page]
Scribd
Upload
35 views4 pages

DBMS Lab Practical 1

This document outlines a practical exercise on retrieving records from an 'EMPLOYEES' database table. It includes SQL commands for creating the database, defining the table structure, inserting employee records, and executing various queries to retrieve specific employee information. The queries cover employee details based on criteria such as manager ID, salary, last name, and department ID.

Uploaded by

ag.adityagaikwad24
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
35 views4 pages

DBMS Lab Practical 1

This document outlines a practical exercise on retrieving records from an 'EMPLOYEES' database table. It includes SQL commands for creating the database, defining the table structure, inserting employee records, and executing various queries to retrieve specific employee information. The queries cover employee details based on criteria such as manager ID, salary, last name, and department ID.

Uploaded by

ag.adityagaikwad24
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/ 4

DATA BASE MANAGEMENT SYSTEM PRACTICAL

NAME = GAIKWAD ROHAN ANIL

LAB PRACTICAL NO. = 1

Question –

1. (Exercise on retrieving records from the table) EMPLOYEES (Employee_Id, First Name,
Last Name, Email, Phone Number, Hire_Date, Job_Id, Salary, Commission_Pct, Manager_Id,
Department_Id)
(a) Find out the employee id, names, salaries of all the employees
(b) List out the employees who works under manager 100
(c) Find the names of the employees who have a salary greater than or equal to 45000
(d) List out the employees whose last name is 'Gaikwad '
(e) Find the names of the employees who works in departments 60,70 and 80
(f) Display the unique Manager_id

Solution -

 CREATE DATABASE employees ;


 USE employees ;
 CREATE TABLE employees (
Employee_id INT(10) PRIMARY KEY ,
first_name CHAR(10) NOT NULL ,
last_name CHAR (10) NOT NULL,
Email VARCHAR(20) NOT NULL ,
Phone_number INT (15) NOT NULL,
Hire_Date DATE NOT NULL,
Job_id VARCHAR(10) NOT NULL,
Salery INT (10) NOT NULL ,
Commision_pct INT (10) NOT NULL ,
Manager_Id (10) NOT NULL ,
Department_id INT (20) NOT NULL ) ;
 DESC employees ;
 INSERT INTO employees (
Employee_id , first_name , last_name , Email, Phone_number,
Hire_Date, Job_id , Salery, Commision_pct , Manager_Id, Department_id )
VALUES
( 1001 , " Rohan "," Gaikwad "," rohan@gmail.com ", 938693 , " 2020-05-25 " , " EI2001 " ,
40000 , 8 , 100 , 60 ),
( 1002 , " Shewta "," Patki " ," Shweta@gmail.com ", 934893 , " 2019-04-15 " , " EI2012 " ,
45000 , 9 , 100 , 80 ) ,
( 1003 , " Ejaj "," Shaikh "," ejaj@gmail.com ", 955693 , " 2020-09-27 " , " EI2008 " ,45000 , 8 ,
110 , 60 ) ,
( 1004 , " Tejal "," Bhavke "," Tejal@gmail.com ", 888693 , " 2018-08-14 " , " EI2008 " ,50000 ,
9 , 100 , 70 );

 SELECT * FROM employees ;

QUESTIONS

QUESTION – (a) Find out the employee id, names, salaries of all the employees :

 SELECT Employee_id , first_name , last_name , salery FROM employees ;

QUESTION – (b) List out the employees who works under manager 100 :

 SELECT * FROM employees WHERE Manager_Id = 100 ;


QUESTION – (c) Find the names of the employees who have a salary greater than or equal to 45000 :

 SELECT * FROM employees WHERE salery >= 45000 ;

QUESTION – (d ) List out the employees whose last name is 'Gaikwad ' :

 SELECT * FROM employees WHERE last_name = " Gaikwad" ;

QUESTION – (e) Find the names of the employees who works in departments 60,70 and 80 :

 SELECT * FROM employees WHERE Department_id IN ( 60 , 70 , 80 ) ;

QUESTION – (f ) Display the unique Manager_Id :

 SELECT distinct ( Manager_Id ) FROM employees ;

You might also like