Skip to content

Master's degree. Relational databases: DDL | DQL commands | Codd's algebra | Complex SQL requests

Notifications You must be signed in to change notification settings

LesiaUKR/goit-rdb-hw-04

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

3 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

goit-rdb-hw-04

Homework Assignment for Topic 4. DML and DDL Commands. Complex SQL Expressions

In this homework assignment, you will have the opportunity to apply your knowledge of SQL language to create and work with a database.

The assignment will reveal the process of defining table structures using DDL commands, populating them with test data using DML commands.

It will also promote skills in writing complex queries using multiple JOIN operators to merge tables.

Description of the homework assignment

Create a database to manage a library book system according to the structure provided below. Use DDL commands to create the necessary tables and their relationships.

1. Create Database

Database Structure

a) Schema name - "LibraryManagement"

b) Table "authors":

author_id (INT, automatically incremented PRIMARY KEY) author_name (VARCHAR) c) Table "genres":

genre_id (INT, automatically incremented PRIMARY KEY) genre_name (VARCHAR) d) Table "books":

book_id (INT, automatically incremented PRIMARY KEY) title (VARCHAR) publication_year (YEAR) author_id (INT, FOREIGN KEY relationship with "Authors") genre_id (INT, FOREIGN KEY relationship with "Genres") e) Table "users":

user_id (INT, automatically incremented PRIMARY KEY) username (VARCHAR) email (VARCHAR) f) Table "borrowed_books":

borrow_id (INT, automatically incremented PRIMARY KEY) book_id (INT, FOREIGN KEY relationship with "Books") user_id (INT, FOREIGN KEY relationship with "Users") borrow_date (DATE) return_date (DATE)

2. Populate the tables with simple fictitious test data. One or two rows in each table are sufficient.

3. Switch to the database you worked with in topic 3.

Write a query using the FROM and INNER JOIN operators to combine all data tables we loaded from files: order_details, orders, customers, products, categories, employees, shippers, suppliers. To do this, you must find the common keys.

Verify the correctness of the query execution.

4. Execute the queries listed below.

  • Determine how many rows you obtained (using the COUNT operator).
  • Change several INNER operators to LEFT or RIGHT. Determine what happens to the number of rows. Why? Write the answer in a text file.
  • Select only those rows where employee_id > 3 and ≤ 10.
  • Group by category name, calculate the number of rows in each group, and the average quantity of product (the quantity of product is in order_details.quantity).
  • Filter the rows where the average quantity of product is greater than 21.
  • Sort the rows by descending order of row count.
  • Display on the screen (select) four rows with a missing first row.

About

Master's degree. Relational databases: DDL | DQL commands | Codd's algebra | Complex SQL requests

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published