Scribd Logo
Upload

Welcome to Scribd!

  • 225 views

    Basic Lab For MySQL

    Uploaded by

    leul
    This document contains instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database, populate the tables with sample data, and write MySQL queries to retrieve and manipulate data from the tables. The tables created are EMPLOYEE, DEPARTMENT, and COURSE, and students are provided example data to insert. They are then given 24 questions asking them to write MySQL queries performing tasks like selecting rows, ordering results, adding/deleting columns, and using operators like LIKE, IN, BETWEEN to filter results.

    Copyright:

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

    Basic Lab For MySQL

    Uploaded by

    leul
    225 views6 pages
    This document contains instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database, populate the tables with sample data, and write MySQL queries to retrieve and manipulate data from the tables. The tables created are EMPLOYEE, DEPARTMENT, and COURSE, and students are provided example data to insert. They are then given 24 questions asking them to write MySQL queries performing tasks like selecting rows, ordering results, adding/deleting columns, and using operators like LIKE, IN, BETWEEN to filter results.

    Original Description:

    lab assignment for MySQL DBMS

    Original Title

    basic lab for MySQL

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database, populate the tables with sample data, and write MySQL queries to retrieve and manipulate data from the tables. The tables created are EMPLOYEE, DEPARTMENT, and COURSE, and students are provided example data to insert. They are then given 24 questions asking them to write MySQL queries performing tasks like selecting rows, ordering results, adding/deleting columns, and using operators like LIKE, IN, BETWEEN to filter results.

    Copyright:

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

    Basic Lab For MySQL

    Uploaded by

    leul
    This document contains instructions for a lab exercise on basic MySQL commands. Students are asked to create tables in a database, populate the tables with sample data, and write MySQL queries to retrieve and manipulate data from the tables. The tables created are EMPLOYEE, DEPARTMENT, and COURSE, and students are provided example data to insert. They are then given 24 questions asking them to write MySQL queries performing tasks like selecting rows, ordering results, adding/deleting columns, and using operators like LIKE, IN, BETWEEN to filter results.

    Copyright:

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

    Mekelle Institute of

    Laboratory manual for


    Technology(MIT)
    Database Management
    Mekelle,Tigray ,Ethiopia

    Systems(DBMSs)
    Lab manual : 4

    Basic MySQL Commands


    For this weeks 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
    MIT/1999/2

    BINIAM
    MEZGEBO

    GEBREKIDAN
    HAILU

    MIT/1999/3
    MIT/2004/1

    TILAHUN
    KIFLE

    KIROS
    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

    MIT/2000/2

    MELES

    YFTER

    MIT/2000/3

    GIRMAY

    TE-AMRAT

    MIT/2000/4

    KIROS

    ABREHA

    MIT/2000/5
    MIT/2000/6

    BIRHANE
    FISEHA

    G/HAWERIA
    WELDAY

    MIT/2000/7

    SIRAJ

    FILUM

    MIT/2000/8

    MIZAN

    ABRHA

    MIT/2000/9

    MEARG

    AREGAWI

    SIRAJ

    MIT/2002/1

    FREWEINI

    GEBRU

    EMP_GENDER

    EMP_AGE

    EMP_SALARY

    26

    2800.0000

    200

    1900.0000

    30

    2000.0000

    45

    2010.0000

    26

    900.0000

    29

    1900.0000

    22

    1700.0000

    20

    1200.0000

    18

    12000.0000

    15

    13000.0000

    29

    11000.0000

    310

    1982.0000

    20

    1900.0000

    27

    4000.000

    18

    21000.0000

    15

    31000.0000

    29

    1000.0000

    301

    2982.0000

    28

    1900.0000

    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

    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

    INFORMATION TECHNOLOGY

    CSE

    COMPUTER SCIENCE AND ENGINEERING

    MIT/2000/2

    ECE

    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

    ELECTRONICS AND COMMUNICATION ENGINEERING

    COURSE TABLE:
    EMP_ID

    COURSE_CODE

    MIT/2004/2

    MIS

    CREDIT_HOURS
    3

    COURSE_NAME
    MANAGEMENT INFORMATION SYSTEMS

    MIT/1999/2
    SYSTEMS

    DBMS

    DATABASE MANAGEMENT

    MIT/2004/1

    DS

    DATA STRUCTURES

    MIT/1999/5

    RAD

    MIT/1999/6

    SAS

    MIT/1999/7

    DC

    DATA COMMUNICATIONS

    MIT/2000/1

    CM

    COMPUTATIONAL METHODS

    MIT/2000/2

    PS

    POWER SYSTEMS

    MIT/2000/5

    MS

    MICROWAVE SYSTEMS

    MIT/2000/6

    DSP

    DIGITAL SIGNAL PROCESSING

    MIT/2000/7

    MC

    MICROCONTROLLERS

    RAPID APPLICATION DEVELOPMENT


    SIGNALS AND SYSTEMS

    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