Scribd Logo
Upload

Welcome to Scribd!

  • 22 views

    DS Lab # 04 1

    Uploaded by

    ashbalfaheem
    This document describes a lab experiment on retrieving data from database tables using SQL SELECT statements. It provides examples of using DISTINCT, ALIAS, WHERE, ORDER BY, LIKE, and BETWEEN clauses. It also lists 15 exercises involving writing SQL queries on an EMP table with columns like EMPNO, ENAME, DEPARTMENT, HIREDATE, SALARY, CITY. The objective is to practice using SELECT statement to retrieve and filter data.

    Copyright:

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

    DS Lab # 04 1

    Uploaded by

    ashbalfaheem
    22 views3 pages
    This document describes a lab experiment on retrieving data from database tables using SQL SELECT statements. It provides examples of using DISTINCT, ALIAS, WHERE, ORDER BY, LIKE, and BETWEEN clauses. It also lists 15 exercises involving writing SQL queries on an EMP table with columns like EMPNO, ENAME, DEPARTMENT, HIREDATE, SALARY, CITY. The objective is to practice using SELECT statement to retrieve and filter 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 describes a lab experiment on retrieving data from database tables using SQL SELECT statements. It provides examples of using DISTINCT, ALIAS, WHERE, ORDER BY, LIKE, and BETWEEN clauses. It also lists 15 exercises involving writing SQL queries on an EMP table with columns like EMPNO, ENAME, DEPARTMENT, HIREDATE, SALARY, CITY. The objective is to practice using SELECT statement to retrieve and filter data.

    Copyright:

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

    DS Lab # 04 1

    Uploaded by

    ashbalfaheem
    This document describes a lab experiment on retrieving data from database tables using SQL SELECT statements. It provides examples of using DISTINCT, ALIAS, WHERE, ORDER BY, LIKE, and BETWEEN clauses. It also lists 15 exercises involving writing SQL queries on an EMP table with columns like EMPNO, ENAME, DEPARTMENT, HIREDATE, SALARY, CITY. The objective is to practice using SELECT statement to retrieve and filter data.

    Copyright:

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

    DEPARTMENT OF TELECOMMUNICATION ENGINEERING

    BACHELOR OF SCIENCE IN CYBER SECURITY


    MEHRAN UNIVERSITY OF ENGINEERING & TECHNOLOGY, JAMSHORO
    DATABASE SYSTEMS
    (2nd SEMESTER, 1st Year) LAB EXPERIMENT # 4

    Name: _________________________________________________________ Roll No: ______________

    Date of Conduct: ___________ Submission Date: _________ Signature of the Lab Tutor: ____________
    ABILITY TO CONDUCT
    LAB PERFORMANCE INDICATOR SUBJECT KNOWLEDGE DATA ANALYSIS AND INTERPRETATION
    EXPERIMENT

    SCORE

    Objective: To retrieve data from tables of a database using SQL SELECT Statement.
    Tools: ORACLE/MySQL/Workbench Duration: 3 Hours

    The SELECT statement is used to select data from a database. The data returned is stored in a
    result table, called the result-set. Some of the Select statements are mentioned below.

    1. The DISTINCT statement is used to return only distinct (different) values.

    Syntax:
    SELECT DISTINCT column1, column2, ...
    FROM table_name;

    2. The SQL ALIAS is used to give a temporary name to a specific column in a table.

    Syntax:
    SELECT column_name(s)
    AS alias_name
    FROM table_name;

    3. The WHERE clause is used to filter records. It is used to extract only those records that
    fulfill a specified condition.

    Syntax:
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

    1|Page
    DEPARTMENT OF TELECOMMUNICATION ENGINEERING
    BACHELOR OF SCIENCE IN CYBER SECURITY
    MEHRAN UNIVERSITY OF ENGINEERING & TECHNOLOGY, JAMSHORO
    DATABASE SYSTEMS
    (2nd SEMESTER, 1st Year) LAB EXPERIMENT # 4
    4. The ORDER BY keyword is used to sort the result-set in ascending or descending order.

    Syntax:
    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... ASC|DESC;

    5. The LIKE operator is used in a WHERE clause to search for a specified pattern in a
    column. Here are some examples showing different LIKE operators with '%'

    • % The percent sign represents zero, one, or multiple characters.


    • _ The underscore represents a single character.

    LIKE Operator Description


    WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
    WHERE CustomerName LIKE '%a' Finds any values that end with "a"
    WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and
    ends with "o"
    WHERE ContactName LIKE '_a%' Finds any value that starts with “a”
    keeping the first character fixed.

    Syntax:
    SELECT column1, column2, ...
    FROM table_name
    WHERE columnName LIKE pattern;

    Example:
    SELECT last_name, job_id, salary
    FROM employees
    WHERE (job_id = 'SA_REP'
    OR job_id = 'AD_PRES')
    AND salary > 15000
    ORDER BY last_name;

    6. The BETWEEN operator selects values within a given range. The values
    can be numbers, text, or dates.

    2|Page
    DEPARTMENT OF TELECOMMUNICATION ENGINEERING
    BACHELOR OF SCIENCE IN CYBER SECURITY
    MEHRAN UNIVERSITY OF ENGINEERING & TECHNOLOGY, JAMSHORO
    DATABASE SYSTEMS
    (2nd SEMESTER, 1st Year) LAB EXPERIMENT # 4
    Syntax:
    SELECT Column_Name FROM Table_name
    WHERE Column_name BETWEEN value1 AND value2;

    Exercise
    Consider following tables to write SQL queries.
    • EMP (EMPNO, ENAME, DEPARTMENT, HIREDATE, SALARY, CITY )
    Set EMPNO as Primary Key.

    Note: Read out all the questions before creating the table.

    1. Write a query to display unique departments from EMP table.


    2. Display the employee name, job, and hiring date of employees hired between February
    20, 2020 and January 18, 2024. Order the query in ascending order by hiring date.
    3. List out the employees whose name start with ‘S’.
    4. List out the employees whose name ends with ‘N’.
    5. Display the names of all employees whose name starts with ‘L’ and ends with ‘A’.
    6. Display the names of all employees whose second letter is “E”.
    7. Display the names of all employees where the third letter of their name is an A.
    8. Display all names of employees whose salary is greater than 50,00 or their department is
    Marketing.
    9. Display all names of employees whose salary is between 50K to 1 Lac;
    10. Display the name of employees whose hiring date is greater than 1999.
    11. Display the name of employees whose salary is not equal to 75,000.
    12. Display the name and salary of employees who earn more than 15000 and are in City is
    Hyderabad.
    13. Write a query that produces the following for each employee:
    ENAME, JOB, HIREDATE, SALARY.
    14. Write a query to display ENAME as EmployeeName, HIREDATE as JoiningDate.
    15. Write a query to display the current salary and incremented salary by 2 times. The table
    should display both current and increased salary.

    3|Page

    You might also like