1 / 4

Seminar 2: Simple SQL Retrievals

Seminar 2: Simple SQL Retrievals. Purpose To write simple SQL queries. To retrieve relations in different order. Scenario The Company database contains data about the departments with their employees, as well as for the department projects with the employees working on them.

klaus
Download Presentation

Seminar 2: Simple SQL Retrievals

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Seminar 2: Simple SQL Retrievals • Purpose • To write simple SQL queries. • To retrieve relations in different order. • Scenario • The Company database contains data about the departments with their employees, as well as for the department projects with the employees working on them. • The set of relations in the Company relational schema is provided on the Figure 1. • Data for one particular instance of database Company is provided on Figure 2. • The SQL script for creating relational database with the above test data preloaded is provided in the file Company.sql which you can download from Blackboard. Seminar #3 CM036: Advanced Databases

  2. Company Relational Schema • DEPT: stores data about company departments, including their managers • EMP: stores data about people working in the company • PROJ: stores data about the projects on which the company currently works • ALLOC: stores data about the people allocated to different projects Figure 1 Seminar #3 CM036: Advanced Databases

  3. Database Schema Instance Figure 2 Seminar #3 CM036: Advanced Databases

  4. Tasks for the Seminar • Creating the Company database. • Download Company.sql from Blackboad and save it in a folder on your U drive, e.g., U:\CM0719. • Connect to the Oracle database using SQL*Plus with the following details: UserName: YourWindwsUserId Password: PASSWORD Host: INFORMATICS • Create and Load the Company database e.g., SQL> @U:\CM0719\Company 2. Write the following queries against Company database using Oracle SQL: • Display name and number of each employee. • Display name and salary of each employee in ascending order. • Display details of projects in descending order of deadline. Seminar #3 CM036: Advanced Databases

More Related