Scribd Logo
Upload

Welcome to Scribd!

  • 61 views

    Lab 5

    Uploaded by

    Mohamed Adel
    This document provides instructions and examples for performing data manipulation operations using SQL, including inserting, updating, deleting, and selecting data from tables. It discusses various SQL statements like INSERT, UPDATE, DELETE, SELECT and clauses like WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Examples are provided to demonstrate inserting multiple rows, selecting distinct values, joining tables, aggregating data, and more. Solutions are also provided for exercises involving importing a database and writing queries to manipulate and retrieve data.

    Copyright:

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

    Lab 5

    Uploaded by

    Mohamed Adel
    61 views17 pages
    This document provides instructions and examples for performing data manipulation operations using SQL, including inserting, updating, deleting, and selecting data from tables. It discusses various SQL statements like INSERT, UPDATE, DELETE, SELECT and clauses like WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Examples are provided to demonstrate inserting multiple rows, selecting distinct values, joining tables, aggregating data, and more. Solutions are also provided for exercises involving importing a database and writing queries to manipulate and retrieve data.

    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 provides instructions and examples for performing data manipulation operations using SQL, including inserting, updating, deleting, and selecting data from tables. It discusses various SQL statements like INSERT, UPDATE, DELETE, SELECT and clauses like WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Examples are provided to demonstrate inserting multiple rows, selecting distinct values, joining tables, aggregating data, and more. Solutions are also provided for exercises involving importing a database and writing queries to manipulate and retrieve data.

    Copyright:

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

    Lab 5

    Uploaded by

    Mohamed Adel
    This document provides instructions and examples for performing data manipulation operations using SQL, including inserting, updating, deleting, and selecting data from tables. It discusses various SQL statements like INSERT, UPDATE, DELETE, SELECT and clauses like WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Examples are provided to demonstrate inserting multiple rows, selecting distinct values, joining tables, aggregating data, and more. Solutions are also provided for exercises involving importing a database and writing queries to manipulate and retrieve data.

    Copyright:

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

    CSCI305-FALL

    LAB 5
     DML (Data Manipulation Language):
     Interact with data records “rows” itself .
     Commands:
    o Insert
    o Update
    o Delete
    o Select

    Inserting Data into Tables:


    To insert data into tables we have 4 main types of insertion.

    #1) MySQL Insert A complete single row:


    Values will be inserted in order of the columns (value1 goes in column 1 and so on):

    INSERT INTO table_name


    VALUES (value1, value2, value3, ...);

    For example:

    insert into employee


    values ('mohamed', 'm', 'fahmy',1 ,'1989-09-19', '10 adan street', 'M',10000, Null,
    Null);

    #2) MySQL Inserting data only In specified column:


    If you do not need to insert data in all fields, you can specify the columns that you wish to insert data
    into.

    INSERT INTO table_name (column1, column2, column3, ...)


    VALUES (value1, value2, value3, ...);
    CSCI305-FALL

    For example:

    INSERT INTO employees ( FirstName, Ssn, Salary )


    VALUES ( 'Mohamed', 22, 2900);

    #3) MySQL Insert multiple rows:


    Next, we will go through the scenario where we have to insert multiple rows into the table with the
    same INSERT statement.

    INSERT INTO employees ( FirstName, Ssn, Salary )


    VALUES
    ( 'Mohamed', 22, 2900 ),
    ( 'Mostafa', 31, 5000 ),
    ( 'Ahmed', 34, 6000 ),
    ( 'Ali', 80, 3000 );

    #4) MySQL Insert into A table from another table:


    Next, we will go through the scenario where we have to insert data in a new table from an existing
    table.

    For Example, Consider a scenario where we have to periodically move data from our existing table to
    a historic or archive table. In order to achieve this, let’s create a new table employees_history.

    Our task is to move data from the employee table to the employees_history table.

    First, we will create employees_history with the same columns of employee table.

    CREATE TABLE employees_history LIKE employee;

    Second, you need to select all data from employee table and insert it into employee_history table.

    INSERT INTO employees_history


    SELECT * FROM employee;
    CSCI305-FALL

    UPDATE Statement:
    The UPDATE statement is used to modify the existing records in a table.

    We can use Where clause for condition.


    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;

    Delete Statement:

    The DELETE statement is used to delete existing records in a table.

    DELETE FROM table_name


    WHERE condition;

    Select Statement:

    The SELECT statement is used to retrieve data from a database.

    SELECT column1, column2, ...


    FROM table_name;

    If you want to select all the fields available in the table, use the following syntax:
    SELECT * FROM table_name;

    For example: Display the full name of the emplo (by using Concatenation).
    SELECT concat(fname, ' ' ,lname) As full_name
    From employee;
    CSCI305-FALL

     Logical and comparison operators:

    Select (Comparisons Involving NULL):


    By using -- > IS / IS NOT NULL.

    E.g.:
    CSCI305-FALL

    Select (like operator):


     The percent sign (%) represents zero, one, or multiple characters
     The underscore sign (_) represents one, single character

    Alias concept:

    DISTINCT:
    CSCI305-FALL

    Aggregate functions
    o Min()
    o Max()
    o Count()
    o Avg()
    o Sum()

    Example: Display maximum and minimum salary for the company.


    select max(salary) as max_salaries, min(salary) as min_salaries
    from employee;

    Example: Display the count of managers in the company.


    select count(mgr_ssn) as num_of_managers
    from department;

    Example: Display the average salaries in the company.


    select avg(salary) as average_salaries
    from employee;

    Example: Display the sum of the salaries in the company.


    select sum(salary) as sum_salaries
    from employee;
    CSCI305-FALL

    ORDER BY:
    The ORDER BY keyword sorts the records in ascending order by default. To sort
    the
    records in descending order, use the DESC keyword.
    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... ASC|DESC;

    LIMIT:
    The LIMIT command is used to specify the number of records to return.

    SELECT column_name(s)
    FROM table_name
    WHERE condition
    LIMIT number;
    CSCI305-FALL

     Group by:
    The GROUP BY statement groups rows that have the same values into summary
    rows, like "find the number of customers in each country".

    The GROUP BY statement is often used with aggregate functions


    (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
    columns.

    Example: Display the average salary for each department.

    select avg(salary)
    from employee
    group by Dno

    Example: For each department, retrieve the department number, the number of
    employees in the department, and their average salary.

    select DNO, COUNT(*) AS Num_of_employees, avg(salary)


    from employee
    group by Dno;

     Having:
    The HAVING clause was added to SQL because the WHERE keyword cannot be used
    with aggregate functions.
    SELECT column_name(s)
    FROM table_name
    WHERE condition
    GROUP BY column_name(s)
    HAVING condition
    ORDER BY column_name(s);
    CSCI305-FALL

    Example: Display the average salary for each department given that its maximum
    salary is greater than 1800

    select avg(salary)
    from employee
    group by Dno
    having max(salary) > 1800;

     Set operations:
     SQL has directly incorporated some set operations
    o There is a union operation (UNION),
    o and in some versions of SQL (MINUS) and intersection (INTERSECT)
     The resulting relations of these set operations are sets of tuples;
    o duplicate tuples are eliminated from the result
     The set operations apply only to union compatible relations;
    o the two relations must have the same attributes and the attributes must
    appear in the same order
    CSCI305-FALL

     Union:

     INTERSECT:
    CSCI305-FALL

     Minus:
    CSCI305-FALL

    Example:

    - Import attached database “company_lab5” on moodle. Follow these


    steps to import:
    CSCI305-FALL

    a) Choose “Data Import” from “Server” tab.

    b) Choose “Import from Self-contained file”


    CSCI305-FALL

    c) Browse “.sql” file from your PC.

    d) Select “Start Import” from “Import Progress” tab.


    CSCI305-FALL

    e) Now you will find “company_lab5” database which we will used.

    Now you can use this database for this example:


    1. Display data of Dependents in company.
    2. Update salary for the employee whose ssn = 3 to “12000”.
    3. Update working hours for employee ssn =2 in Project
    number = 125 to 8 hours.
    4. Delete „Wafaa‟ employee from company.
    5. For each department, display the number of employees in
    the department.
    6. Display the sum of salaries in company
    7. Display the full name of the employees.
    8. Display the fname and ssn of the employees ordered by
    fname descendingly.
    9. Retrieve all distinct salary values of employees.
    10. Retrieve all employees in department 1 whose salary
    between 5000 $ and 10000 $
    CSCI305-FALL

    Solutions:
    1. SELECT * FROM Dependent;

    2. UPDATE Employee
    set Salary=12000
    where Ssn=3;

    3. Update Works_On
    set hours=8
    where Essn=2 and Pno=125 ;

    4. DELETE FROM Employee


    where Fname ='Wafaa';

    Hint: this will give an error “Safe update mode”, to remove it go to “Edit” tab  “Preferences” ”SQL
    editor”  uncheck “safe updates….”  “Query” tab  “reconnect to server”

    5. Select COUNT(*) As count_of_employees, Dno


    FROM Employee
    Group by Dno;

    6. SELECT SUM(Salary)
    From Employee;

    7. SELECT concat (fname, ' ' ,lname) As full_name


    From Employee;

    8. SELECT fname, SSN


    From Employee
    ORDER BY fname DESC;

    9. SELECT DISTINCT Salary FROM employee;

    10. SELECT * FROM employee


    WHERE (Salary between 5000 AND 10000) AND dno='1';
    CSCI305-FALL

    Lab 5 Exercise

    Import attached database “Hotel_lab5” on moodle. Submit “.sql” file


    Please do the following Queries for the data in Hotels database:
    1- List all double or family rooms with a price above 40.00$ per night, in ascending order
    of price.
    2- Update room price to be double of price.
    3- List full details of all hotels.
    4- List full details of hotels in London.
    5- List the names and addresses of all guests in London, alphabetically ordered by name.
    6- List the bookings for which no date_to has been specified.
    7- Select numbers of hotels in database.
    8- Retrieve average price of a room
    9- Select the total revenue per night from all double rooms.

     Created by Moaaz Khairy 


    ‫ال تنسونا من دعائكم‬

    You might also like