seminar 1 revisiting databases and sql
Download
Skip this Video
Download Presentation
Seminar 1: Revisiting Databases and SQL

Loading in 2 Seconds...

play fullscreen
1 / 6

Seminar 1: Revisiting Databases and SQL - PowerPoint PPT Presentation


  • 136 Views
  • Uploaded on

Seminar 1: Revisiting Databases and SQL. Purpose To refresh basic concepts of databases. To refresh SQL. Scenario – Human Resources (HR)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Seminar 1: Revisiting Databases and SQL' - lequoia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
seminar 1 revisiting databases and sql
Seminar 1: Revisiting Databases and SQL
  • Purpose
    • To refresh basic concepts of databases.
    • To refresh SQL.
  • Scenario – Human Resources (HR)
    • In the company\'s human resource records, each employee has a unique identification number, email address, job identification number, salary, and manager. Some employees earn a commission in addition to their salary, which is also tracked.
    • The company also tracks information about jobs within the organization. Each job has an identification number, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different jobs within the company. When an employee switches jobs, the company records the start date and end date of the former job, the job identification number, and the department.
    • The sample company is regionally diverse, so it tracks the locations of not only its warehouses but also of its departments. Each of the company\'s employees is assigned to a department. Each department is identified by a unique department code and a short name. Each department is associated with one location. Each location has a full address that includes the street address, postal code, city, state or province, and country code.
    • For each where it has facilities, the company records the country name, currency symbol, currency name and the region where the county resides geographically.

Seminar #1 – Refreshing databases concepts and SQL

CM036: Advanced Databases

hr relational schema
HR Relational Schema
  • Entity Relationship Diagram (ERD)
  • Creating Schema
    • Download the script file: oracle_script.sql.txt into U:\CM036\W1 from the Blackboard or http://computing.unn.ac.uk/staff/cgma2/cm036/Week1/
    • Run the script at the SQL* Plus prompt
      • e.g. @U:\CM036\W1\oracle_script.sql.txt

Seminar #1 – Refreshing databases concepts and SQL

CM036: Advanced Databases

sample table descriptions
Sample Table Descriptions

Seminar #1 – Refreshing databases concepts and SQL

CM036: Advanced Databases

sample table descriptions4
Sample Table Descriptions …

Seminar #1 – Refreshing databases concepts and SQL

CM036: Advanced Databases

tasks for the seminar
Tasks for the Seminar
  • Discuss the following questions with the student next to you:
    • What is a database?
    • What are databases used for?
    • What is a relational database?
    • What are the basic elements of the relational data model?
  • Write SQL queries using Oracle SQL* Plus to answer the following:
    • Show the structure of the DEPARTMENTS table. Select all data from the table.
    • Create a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first.
    • Create a query to display unique job codes from the EMPLOYEES table.
    • Create a query to display the last name and salary of employees earning more than 12,000.
    • Display the employee last name, job ID, and start date of employees hired between February 20, 1998, and May 1, 1998. Order the query in ascending order by start date.
    • Write a query to display the last name, department number, and department name forall employees.
    • Display the employee last name and department name for all employees who have an a (lowercase) in their last names.

Seminar #1 – Refreshing databases concepts and SQL

CM036: Advanced Databases

tasks for the seminar6
Tasks for the Seminar …
  • Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.
  • Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.
  • Display the names and hire dates for all employees who were hired before their managers, along with their manager’s names and hire dates. Label the columns Employee, Emp Hired, Manager, and MgrHired, respectively.

Seminar #1 – Refreshing databases concepts and SQL

CM036: Advanced Databases

ad