Scribd Logo
Upload

Welcome to Scribd!

  • 92 views

    21bce9836 DBMS Lab Assignment-1

    Uploaded by

    RAMIREDDY LAKSHMI PRASANNA 21BCE9890
    The document describes lab experiments conducted by a student to create and populate database tables. It includes: 1. Creating an "Employee" table with attributes like name, SSN, salary, and a "Department" table with attributes like name and manager details. 2. Inserting sample data into the Employee and Department tables, including names, IDs, salaries for 5 employees and department names and managers. 3. Writing SQL queries to retrieve specific data from the tables, such as employee names and IDs, details of employees with a particular birthdate, unique salaries, and manager details for the finance department.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    21bce9836 DBMS Lab Assignment-1

    Uploaded by

    RAMIREDDY LAKSHMI PRASANNA 21BCE9890
    92 views7 pages
    The document describes lab experiments conducted by a student to create and populate database tables. It includes: 1. Creating an "Employee" table with attributes like name, SSN, salary, and a "Department" table with attributes like name and manager details. 2. Inserting sample data into the Employee and Department tables, including names, IDs, salaries for 5 employees and department names and managers. 3. Writing SQL queries to retrieve specific data from the tables, such as employee names and IDs, details of employees with a particular birthdate, unique salaries, and manager details for the finance department.

    Original Title

    21BCE9836 DBMS LAB ASSIGNMENT-1

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes lab experiments conducted by a student to create and populate database tables. It includes: 1. Creating an "Employee" table with attributes like name, SSN, salary, and a "Department" table with attributes like name and manager details. 2. Inserting sample data into the Employee and Department tables, including names, IDs, salaries for 5 employees and department names and managers. 3. Writing SQL queries to retrieve specific data from the tables, such as employee names and IDs, details of employees with a particular birthdate, unique salaries, and manager details for the finance department.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    92 views7 pages

    21bce9836 DBMS Lab Assignment-1

    Uploaded by

    RAMIREDDY LAKSHMI PRASANNA 21BCE9890
    The document describes lab experiments conducted by a student to create and populate database tables. It includes: 1. Creating an "Employee" table with attributes like name, SSN, salary, and a "Department" table with attributes like name and manager details. 2. Inserting sample data into the Employee and Department tables, including names, IDs, salaries for 5 employees and department names and managers. 3. Writing SQL queries to retrieve specific data from the tables, such as employee names and IDs, details of employees with a particular birthdate, unique salaries, and manager details for the finance department.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 7

    LabExperiment-1

    Student name: G.VENKATA RAMANA


    Reg. no.: 21BCE9836

    1)Create Table Employee with attributes


    firstName,LastName,SSN,Address,Salary,Birthd
    ay, Sex,SupervisorSSN,DepartmentNo.
    CODE:
    mysql> CREATE TABLE EMPLOYEE (
    -> empID INT PRIMARY KEY,
    -> firstName TEXT NOT NULL,
    -> lastName TEXT NOT NULL,
    -> SSN INT UNIQUE NOT NULL,
    -> address TEXT NOT NULL,
    -> salary MEDIUMINT NOT NULL,
    -> birthday DATE NOT NULL,
    -> sex CHAR NOT NULL,
    -> supervisorSSN INT NOT NULL,
    -> departmentNo TINYINT NOT NULL
    -> );
    Query OK, 0 rows affected (0.02 sec)

    1
    2)Create a Table Department with
    attributes
    DNo,DNAMe,ManagerSSN,MgrStartdate.

    CODE:
    mysql> CREATE TABLE DEPARTMENT (
    -> Dno TINYINT NOT NULL,
    -> DName VARCHAR(255) NOT NULL,
    -> ManagerSSN MEDIUMINT NOT NULL,
    -> MgrStartDate DATE NOT NULL
    -> );
    Query OK, 0 rows affected (0.01 sec)

    2
    3)Insert the data given above in both employee,
    department and project tables.
    CODE:
    EMPLOYEE

    mysql> INSERT INTO EMPLOYEE VALUES (0001,


    'RAMANA','GUDA',97643,'NELLORE',200000,'2003-9-11','F',1234,03);
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO EMPLOYEE VALUES


    (0002,'RAVI','KANDULA',94532,'HYDERABAD',120000,'2004-03-
    04','M',1256,01);
    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT INTO EMPLOYEE VALUES
    (0003,'PRANEETH','KANNU',98521,'WARANGAL',125000,'2004-03-
    20','F',1213,02);
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO EMPLOYEE VALUES


    (0004,'RAMANUJA','CHANDURI',94321,'BANGALORE',150000,'2004-04-
    28','M',1243,01);
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO EMPLOYEE VALUES (0005, 'LIKHITH',


    'CHALLA', 97513, 'TIRUPATI', 150000, '1959-03-29', 'F', 1235, 02);
    Query OK, 1 row affected (0.00 sec)

    3
    Department
    CODE:
    mysql> INSERT INTO DEPARTMENT VALUES (01, 'Computer Science',
    98512, '2021-11-01');
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO DEPARTMENT VALUES (02, 'finance', 91511,


    '2020-12-20');
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO DEPARTMENT VALUES (03, 'Mechanical', 92991,


    '2023-01-01');
    Query OK, 1 row affected (0.00 sec)

    mysql> SELECT * FROM DEPARTMENT;

    4
    (TO DISPLAY INFORMATION ABOUT DEPARTMENT TABLE)

    4)Display all the employees’ information.


    CODE:
    mysql> SELECT * FROM EMPLOYEE;

    5)Display Employee name along with his SSN


    and Supervisor SSN.
    CODE:SELECT firstname,SSN,supervisorSSN from EMPLOYEE;

    5
    6)Display the employee names whose bdate is
    ’29-MAR-1959’.
    CODE:
    mysql> SELECT * from EMPLOYEE WHERE birthday= '1959-03-29';

    7)Display salary of the employees without


    duplications.
    CODE:
    mysql> SELECT DISTINCT SALARY from EMPLOYEE;

    6
    8)Display the MgrSSN, MgrStartDate of the
    manager of ‘Finance’ department.
    CODE:
    mysql> SELECT ManagerSSN, MgrStartDate FROM DEPARTMENT
    where DName = 'finance';

    You might also like