Scribd Logo
Upload

Welcome to Scribd!

  • 285 views

    Mekelle Institute of Technology (MIT) Mekelle, Tigray, Ethiopia

    Uploaded by

    GemmedaMidakso
    The document provides instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database to store employee, department, and course data, and populate the tables with sample records. A series of queries are then listed that retrieve, filter, and aggregate data from the tables to demonstrate different MySQL functions.

    Copyright:

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

    Mekelle Institute of Technology (MIT) Mekelle, Tigray, Ethiopia

    Uploaded by

    GemmedaMidakso
    285 views6 pages
    The document provides instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database to store employee, department, and course data, and populate the tables with sample records. A series of queries are then listed that retrieve, filter, and aggregate data from the tables to demonstrate different MySQL functions.

    Original Title

    Lab_manual_4

    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 provides instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database to store employee, department, and course data, and populate the tables with sample records. A series of queries are then listed that retrieve, filter, and aggregate data from the tables to demonstrate different MySQL functions.

    Copyright:

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

    Mekelle Institute of Technology (MIT) Mekelle, Tigray, Ethiopia

    Uploaded by

    GemmedaMidakso
    The document provides instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database to store employee, department, and course data, and populate the tables with sample records. A series of queries are then listed that retrieve, filter, and aggregate data from the tables to demonstrate different MySQL functions.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 6

    Mekelle Institute of Laboratory manual for

    Technology(MIT) Database Management


    Mekelle,Tigray ,Ethiopia
    Systems(DBMSs)

    Lab manual : 4

    Basic MySQL Commands

    For this week’s lab exercise you are going to prepare a document that contains
    all your work and submit it for grading. The first three lines of your document
    should contain your name, id number and lab exercise number like what is
    shown in the example below.

    Example:

    Name =Abebe Belay


    ID no. = your id number
    Lab exercise no. = Lab04

    Make sure you include an answer for all the questions in the lab manual and
    provide a comment for each statement. Your MySQL statements should look
    like this:

    -- Retrieve all rows from employee table

    SELECT * FROM employee

    -- Get count of all rows in employee table

    SELECT COUNT (*) FROM employee

    1. Create the following tables in the database CSEIT that you have created
    during lab exercise 3. These tables are going to be used for this lab exercise
    and for the future lab-exercises. The schema of each table is shown below.
    Be careful to choose the best data type for each column and to follow the
    general naming conventions. Students who ignore such rules are going to
    lose some marks. You should have to identify the appropriate primary key
    for each table. Remember that every table in the database must have a
    primary key to enforce the entity integrity and if there is a relationship
    between tables a foreign key is necessary. Also apply some useful
    constraints while creating your tables.
    EMPLOYEE :( EMP_ID, EMP_FNAME, EMP_LNAME, EMP_GENDER, EMP_AGE,
    EMP_SALARY).
    DEPARTMENT :( EMP_ID, DEPT_CODE, DEPT_NAME).
    COURSE :( EMP_ID, COURSE_CODE, CREDIT_HOURS, COURSE_NAME)

    2. After creating the tables, populate each table with the following data:
    EMPLOYEE TABLE:

    EMP_ID EMP_FNAME EMP_LNAME

    MIT/1999/1 BINIAM GEBREKIDAN


    MIT/1999/2 MEZGEBO HAILU

    MIT/1999/3 TILAHUN KIROS


    MIT/2004/1 KIFLE BERNAHE

    MIT/2004/2 TEKLAY GEBREZGIABHER

    MIT/1999/5 SEYUM TESFAY

    MIT/1999/6 KIROS SYUM

    MIT/1999/7 AKLIL ZENEBE

    MIT/1999/8 BARAKI HALEFOM

    MIT/1999/9 ASSEFA G/WERGIS

    MIT/1999/10 TEKLEMARIAM TSEGAY

    MIT/2000/1 ASMELASH TSEGAY

    MIT/2001/1 AWEL NURHUSEN

    MIT/2001/2 MUNIR SIRAJ

    MIT/2000/2 MELES YFTER

    MIT/2000/3 GIRMAY TE-AMRAT

    MIT/2000/4 KIROS ABREHA

    MIT/2000/5 BIRHANE G/HAWERIA


    MIT/2000/6 FISEHA WELDAY

    MIT/2000/7 SIRAJ FILUM

    MIT/2000/8 MIZAN ABRHA

    MIT/2000/9 MEARG AREGAWI


    MIT/2002/1 FREWEINI GEBRU

    EMP_GENDER EMP_AGE EMP_SALARY

    F 26 2800.0000

    M 200 1900.0000

    M 30 2000.0000

    M 45 2010.0000

    M 26 900.0000

    M 29 1900.0000

    M 22 1700.0000

    M 20 1200.0000

    M 18 12000.0000

    M 15 13000.0000

    M 29 11000.0000

    M 310 1982.0000

    M 20 1900.0000

    M 27 4000.000

    M 18 21000.0000

    M 15 31000.0000

    M 29 1000.0000

    M 301 2982.0000

    M 28 1900.0000

    F 20 1980.000
    DEPARTMENT TABLE:
    EMP_ID DEPT_CODE DEPT_NAME

    MIT/1999/1 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/1999/2 IT Information Technology

    MIT/1999/3 IT INFORMATION TECHNOLOGY

    MIT/1999/4 IT INFORMATION TECHNOLOGY

    MIT/1999/5 IT INFORMATION TECHNOLOGY

    MIT/1999/6 ECE ELECTRONICS AND COMMUNICATION ENGINEERING

    MIT/1999/7 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/2000/1 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/2001/1 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/2001/2 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/2004/2 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/2000/2 ECE ELECTRONICS AND COMMUNICATION ENGINEERING

    MIT/2000/3 CSE COMPUTER SCIENCE AND ENGINEERING

    MIT/2000/4 EEE ELECTRICAL AND ELECTRONICS ENGINEERING

    MIT/2000/5 ECE ELECTRONICS AND COMMUNICATION ENGINEERING

    MIT/2000/6 ECE ELECTRONICS AND COMMUNICATION ENGINEERING

    MIT/2000/7 EEE ELECTRICAL AND ELECTRONICS ENGINEERING

    MIT/2000/8 EEE ELECTRICAL AND ELECTRONICS ENGINEERING

    MIT/2000/9 ECE ELECTRONICS AND COMMUNICATION ENGINEERING

    MIT/2004/1 ECE ELECTRONICS AND COMMUNICATION ENGINEERING

    COURSE TABLE:
    EMP_ID COURSE_CODE CREDIT_HOURS COURSE_NAME

    MIT/2004/2 MIS 3 MANAGEMENT INFORMATION SYSTEMS


    MIT/1999/2 DBMS 4 DATABASE MANAGEMENT
    SYSTEMS

    MIT/2004/1 DS 4 DATA STRUCTURES

    MIT/1999/5 RAD 4 RAPID APPLICATION DEVELOPMENT

    MIT/1999/6 SAS 4 SIGNALS AND SYSTEMS

    MIT/1999/7 DC 3 DATA COMMUNICATIONS

    MIT/2000/1 CM 3 COMPUTATIONAL METHODS

    MIT/2000/2 PS 3 POWER SYSTEMS

    MIT/2000/5 MS 4 MICROWAVE SYSTEMS

    MIT/2000/6 DSP 4 DIGITAL SIGNAL PROCESSING

    MIT/2000/7 MC 4 MICROCONTROLLERS

    3. Write a query to retrieve all rows from the EMPLOYEE table

    4. Write a query that retrieves the details of the employees from the EMPLOYEE table arranged by
    age in ascending order

    5. ADD a column named WORKING_HRS in the EMPLOYEE table and insert valid values into that
    column.

    6. Drop the column that you have added in question 5.

    7. Change the data type of the column EMP_FNAME to char (30) and change it again into varchar (30).

    8. Add a constraint called const_check that checks the salary of a specific employee is greater than
    100 birr.

    9. Add a default value for the column EMP_GENDER to be Male.

    10. Write a query that gives you information about the employees and their department.

    11. Write a query that gives you information about courses and the instructors handling these courses.

    12. Write a query that retrieves all rows from the EMPLOYEE table where the age of the employees
    is greater than 20.

    13. Using IN write a query that retrieves the details of all the employees who are working in the
    department of Information Technology.

    14. Write a query that retrieves the details of the eldest employee.

    15. Write a query that retrieves every detail of the departments order by department name.
    16. Now rewrite the query in 15 in a way that it will retrieve a list of unique departments only.

    17. Remember that using the COUNT (*) aggregate function returns a count of all rows in a table.
    Write a query to return a count of the rows in the COURSES table.

    18. If you want to avoid using an asterisk with COUNT (*), it is possible to use select column name,
    COUNT (column name).Write the query number 17 using this method. Is there a difference with the
    former one?

    19. LIKE statement allows you to use wildcards in your comparisons. Using LIKE statement write a
    query that lists the details of all employees whose first name start with the letter ‘M’

    20. IN also allows you to compare a single column to a list of values. For example, if you want to
    retrieve a list of all employees whose age is 20, 25, or 30. Write a query using IN that can accomplish
    this task.

    21. BETWEEN allows you to check if a column contains a value that is greater than or equal to one
    value and less than or equal to another. Write a query that lists all employees whose age is between 20
    and 30.

    22. Write a query that returns the details of male employees who are elder than 30 and with salary
    greater than or equal to 1800.00birr.

    23. Write a query that returns the details of employees who are not elder than 30 and whose salary is
    not equal to 1800.00birr.

    *24. Write a query that retrieves the names of employees whose salary is greater than mine. {You will
    get a bonus for this.}

    You might also like