---
title: Database Systems Pre-Lab 3
---
<h1 style='border: none'><center>Database Systems Pre-Lab 3</center></h1>
<h2 style='border: none'><center>Basic SQL</center></h2>
<h5><center>The Islamic University of Gaza<br>Engineering Faculty<br>Department of Computer Engineering</center></h5>
<h6>Authors: Usama R. Al Zayan & Rasha E. Kahil<span style="float:right">2023/02/06</span></h6>
---
## Expecteed outcomes
1. To be introduced to the supported and most used data types in PostgreSQL.
2. To be familiar with syntax and semantic rules of PostgreSQL constructs.
3. To practice SQL basics.
4. To build different structured queries for given natural language queries.
## Lab 2: Time and Plan
| Tasks | Timing |
| -------- | -------- |
| Quiz 1 | 10 min |
| Task 1 | 35 min |
| Task 2 | 15 min |
| Task 3 | 15 min |
| Task 4 | 20 min |
## How to show UNI1 database
1. Right click on postgres connection then click `Edit Connection`.
<center>
</center>
2. Click on `PostgreSQL`.
<center>
</center>
3. Check `Show all databases`, then `OK`.
<center>
</center>
## Student’s To Do list:
1. Delete `uni-space`, `uni-largeSpace` and `public` schemas, right click then `Delete`.
<center>
</center>
2. Repeat the first lab again to review how to create a new schema, create new tables, and import data from files.
3. Read and apply the pre-lab 3 carefully.
## Quiz in Lab 03
A quiz will be given at the beginning of lab 02 in this pre-lab.
## Terminology
**$DDL$**: Data-definition language; the part of query language that provides commands for defining relation schemas, specifying of integrity constraints, deleting relations, and modifying relation schemas.
**$DML$**: Data-manipulation language; the part of query language which includes commands for inserting tuples into, deleting tuples from, and modifying tuples in the database.
**$Query$**: is an inquiry to the database for information, usually issued to the database through an application interface or via a command-line prompt.
## Data Definition Language (DDL)
- First exercise; let’s take a quick tour in DDL.sql file which we executed in lab1.
### DDL constructs
The most used DDL commands in PostgreSQL:
1. Create 2. Drop 3. Alter
Carefully read the following schema definition script, followed by different DDL commands:
```sql=
--postgresql supports [IF EXISTS] to avoid the error messages
DROP table if exists books;
CREATE TABLE books (
ISBN varchar(15) primary key,
title text not null,
price numeric,
constraint positive_price check (price >= 0.00) --table level constraint--
);
create table person (
ID varchar(15) primary key,
nationality varchar(20),
phone_no varchar(14) not null,
mobile_no varchar(14)
);
CREATE table author(
auth_ID varchar(15) references person(ID),
auth_name text not null ,
primary key (auth_ID)
);
create table writes (
auth_ID varchar(15),
book varchar(15),
primary key (auth_ID,book)
);
--add column--
alter table author add if not exists auth_DOB date;
--change column data type--
alter table author alter auth_name type varchar(20);
--rename table--
alter table author rename to authors;
--rename column--
alter table authors rename column auth_DOB to DoB;
--change/drop default--
ALTER TABLE books ALTER COLUMN price SET DEFAULT 9.99;
ALTER TABLE books ALTER COLUMN price drop DEFAULT;
```
Constraints are used to make sure that the integrity of data is maintained in the database.
Following are the most common constraints that can be applied to data:
1. CHECK. 2. Not Null. 3. Unique. 4. Primary Key 5. Foreign Key.
Constraints can be identified on one of two levels: Column level, and Table level.
You can name constraints using the key word CONSTRAINT followed by an identifier before the constraint definition. This clarifies error messages and allows you to refer to the constraint when you need to drop or change it.
```sql=
--add constraints—
alter table person ALTER column mobile_no set not null; --column constraint—
alter table writes add constraint fk_writes1
foreign key (auth_ID) references authors(auth_ID); --table constraint—
alter table writes add constraint fk_writes2
foreign key (book) references books(ISBN);
--remove constraint—
alter table IF EXISTS writes drop constraint IF EXISTS fk_writes3;
alter table IF EXISTS person ALTER column phone_no drop not null;
--modify constraint—
ALTER TABLE writes DROP CONSTRAINT fk_writes1,
ADD CONSTRAINT fk_writes1 foreign key (auth_ID) references authors(auth_ID);
```
Notice referential integrity (foreign keys constraints) of our data, which guarantees that no one can insert rows in the “writes” table that does not have a matching entry in both the “books” and “authors” tables. when a user tries inserting an invalid record, an error message appears:
(Error: insert or update on table "writes" violates foreign key constraint "writes_book_fkey" DETAIL:
-- Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to practice them now and in the future.
### PostgreSQL supported data types
Following, the most-used data-types are listed.
1. $Boolean$ Is used to store true, false and null values.
Accepted Boolean values in PostgreSQL:
* True: true,‘t’, ‘true’, ‘y’, ‘yes’, ‘1’, ‘on’.
* False: false, ‘f’, ‘false’, ‘n’, ‘no’, ‘0’, ‘off’.
* Null.
2. $Primary$ $character$ $types$
* **VARCHAR(n)**: a variable-length with limit of n characters, where n is positive integer.
* **CHAR(n)**: fixed-length of n characters, blank padded.
* **TEXT, VARCHAR**: variable unlimited length.
3. $Integers$
* **SMALLINT**: a 2-byte signed integer that has a range from -32,768 to 32,767.
* **INT**: a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647. [Common choice]
* **BIGINT**: an 8-byte signed integer that has a large-range integer.
* **SERIA**L: Same as an integer except that PostgreSQL will automatically generate and populate values.
4. $Floating-point$ $number$
* **FLOAT(n)**: is a floating-point number whose precision is at least, n, up to a maximum of 8 bytes.
* **REAL**: is an inexact, variable-precision numeric type, 4-byte floating-point number.
* **DECIMAL or NUMERIC(precision, scale)**: is a real number with a user-specified precision.
* Especially recommended for storing quantities where exactness is required.
* NUMERIC without a specified precision is subject to the limits up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
4. $Temporal$ $data$ $types$
* **DATE**: stores the dates only.
* **TIME**: stores the time of day values.
* **TIMESTAMP**: stores both date and time values.
* **TIMESTAMPTZ**: is a timezone-aware timestamp data type.
* **INTERVAL**: stores intervals of time.
### User Defined Data Types
PostgreSQL permits the creation of user defined data types. These data types are created using the CREATE DOMAIN and CREATE TYPE commands. The CREATE DOMAIN creates a user-defined data type with constraints such as NOT NULL and CHECK. The CREATE TYPE is often used to create a composite type.
```sql=
create domain year as char(4) NOT NULL CHECK (value ~ '[0-9]{4}');
CREATE TYPE movie_summary AS (
title VARCHAR,
release_year YEAR,
ticket price decimal
);
create table movies (
mov serial primary key,
summary movie_summary
);
insert into movies values ( default , ('STRONGERS','2020',99.9));
```
### ENUM Type
PostgreSQL supports Enumerated types, which are data types that comprise a static, ordered set of values.
The ordering of the values in an enum type is the order in which the values were listed when the type was created. Enum labels are case sensitive.
```sql=
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
```
## Data Manipulation Language (DML)
The most common DML commands of PostgreSQL:
1. Insert 2. Update 3. Delete
```sql=
insert into classroom (room_number,building,capacity)
values ( '101', 'Packard','500');
insert into classroom values ('Painter', '514', '10');
insert into department values ('Biology', 'Watson', '90000');
update department set building = 'Taylor' where dept_name = 'Biology';
delete from department where dept_name = 'Elec. Eng.' ;
--dangerous queries—
delete from prereq;
--warning appears: You are about to execute DELETE statement
--without a WHERE clause on “prereq”. Possible data loss.
delete;
--warning appears: You are about to execute DELETE statement
--without a WHERE clause on “multiple tables”. Possible data loss.
```
Notes:
- In insert statement, you should list attributes if you are not sure of table attributes’ order.
- Drop command is DDL command which deletes both tuples and schema of a relation, where Delete is a DML command which deletes tuples. Be carful when using delete and drop commands.
## Query Language
There are six main parts in structured queries.
Remember the processes you learned; SQL execution order is different from that of its syntax. Our challenge today is to keep remembering the order, and role of each part in query.
<center>
</center>
### SELECT
$SELECT$: is for retrieving data you want according to the columns in a table. SELECT keyword in a query is followed by a list of columns that you want displayed as part of the query output. Aggregate functions [count, max, min, sum, avg] can be listed as well as columns.
### FROM
$FROM$: is used in conjunction with the SELECT keyword. It tells the database what table(s) to access to retrieve the desired data for the query. Joins can also be specified this clause.
### WHERE
$WHERE$: is used to specify the condition of a query that is used to display selective information.
**Condition operators**: Comparison and Logical Operators, conditions can be combined with AND, OR, and NOT operators.
Other Logical operators that return (true or false):
* $NOT NULL$: compare a value with a NULL value.
* $IN$: to compare a value to a list of literal values that have been specified.
* $BETWEEN$: search for values that are within a set of values between minimum and maximum.
* $LIKE$: to compare a value to similar values using wildcard (%, _) operators.
* $EXIST$: to search for the presence of a row in a specified table that meets certain criteria.
### GROUP BY
$GROUP BY$: collaborates with SELECT clause to arrange data into groups, tuples that have the same values in all grouping attributes will appear as a single tuple. GROUP BY is often used with aggregate functions to group the result sets which usually have one or more attributes in common.
### HAVING
$HAVING$: is used in conjunction with the GROUP BY clause in a SELECT statement, tells which groups to include in the output. HAVING is to GROUP BY as WHERE is to SELECT.
### ORDER BY
$ORDER BY$: arranges the results of a query in a listing format you specify, ascending [default order] or descending. A column listed here can be abbreviated with an integer, identifying the position of the column after the SELECT keyword. You can order by multiple columns in a query.
### LIMIT
$LIMIT$: is used to constrain the number of rows returned by query. However, keep in mind that the relational model does not specify an ordering for rows in a relation. Therefore, there is no guarantee on which rows from the result are returned, unless the query itself includes an ORDER BY clause, which sorts results by some expression. Nevertheless, LIMIT is very useful for playing around with a large database and sampling data from tables in it.
## Lab Schema
All queries in the following section are assumed to be applied on “uni-largeSpace” schema you created in Lab 01.
<center>
</center>
## Simple retrieving and ordering queries
Example1: Find the names of all the instructors from the Biology department.
```sql
select name from instructor where dept_name = 'Biology';
```
<center>
</center>
Example2: Find the names of courses in the Computer science department which have 3 credits
```sql=
select title
from course
where dept_name = 'Comp. Sci.' and credits = 3 ;
```
<center>
</center>
Example3: For the student with ID 56232, show all course_id and title of
all courses registered for by the student, ordered ascending by titles.
```sql=
select course_id, title
from takes natural join course
where id = '56232'
order by title;
--or
select takes.course_id, title
from takes, course
where id = '56232'
and takes.course_id = course.course_id
order by title;
```
<center>
</center>
We will use ‘natural join’ in our following examples.
Example 4: Find the highest and lowest salaries of any instructor (without aggregation).
```sql=
(select 'MAX',salary
from instructor
order by salary desc
limit 1)
union
(select 'MIN', salary
from instructor
order by salary
limit 1);
```
<center>
</center>
## More complex queries
Example 5: Find the names of all instructors who earn more than the lowest paid instructor in the Biology department.
```sql=
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary
and S.dept_name = 'Biology';
```
<center>
</center>
Example 6: Find the names of instructors with salary amounts between $90,000 and $100,000 in departments of budgets between $440000 and $777000.
(Optionally: Use row constructor in writing your filter)
```sql=
select name
from instructor natural join department
where (salary, budget)
between (90000 , 444000) and (100000 , 777000);
```
<center>
</center>
Example 7: Find names and salaries of instructors who earn more than ‘Bietzk’.
```sql=
select I.name, I.salary
from instructor as W , instructor as I
where I.salary > W.salary
AND W.name = 'Bietzk';
```
<center>
</center>
## String operations
Example 8: Find the names of all departments whose name ends with 'tics'.
```sql=
select dept_name
from department d
where dept_name like '%tics';
```
<center>
</center>
Example 9: Find the total budget of engineering departments.
```sql=
select sum (budget) as "Engineering budget"
from department
where lower(dept_name) like '%eng%'
and lower(dept_name) not ilike '%engl%'; -- to exclude English
-- or --
select sum (budget) as "Engineering budget"
from department
where dept_name ilike '%eng%'
and dept_name not ilike '%engl%';
```
<center>
</center>
The ILIKE operator - Insensitive LIKE - can be used instead of LIKE to make the match case-insensitive. This is not in the SQL standard but is a PostgreSQL extension.
In PostgreSQL, the operator `~~` is equivalent to LIKE, and `~~*` corresponds to ILIKE.
There are also `!~~` and `!~~*` operators that represent NOT LIKE and NOT ILIKE, respectively.
Example 10: Find no. of characters without considering the spaces for each course name.
```sql=
select distinct title,
length(replace(title, ' ', ''))
from course c;
```
<center>
</center>
Example 11: Find students whose IDs are palindrome numbers.
```sql=
select *
from student s
where reverse(id)=id;
```
<center>
</center>
Example 12: Find instructors whose name has ‘r’ as the 3th character from the last.
```sql=
select *
from instructor
where substring(right(name, 3), 1, 1)= 'r';
```
<center>
</center>
Example 13: Find each course whose title is a single word.
```sql=
select title
from course
where title not like '% %';
```
<center>
</center>
## Grouping and aggregation
Example 14: Find the total number of instructors who teach a course in the spring 2009 semester.
```sql=
select course_id, count(distinct id)
from teaches natural join section
where semester='Spring' and year = 2009
group by course_id;
```
<center>
</center>
Example 15: Find the average salary in each department.
```sql=
select dept_name, avg(salary)
from instructor
group by dept_name;
```
<center>
</center>
Example 16: Find the department who has 12 courses or more.
```sql=
select dept_name, count(distinct course_id)
from course
group by dept_name
having count(distinct course_id) >= 12;
```
<center>
</center>
Example 17: Show name of students who retakes any course more than two times
in years between 2005 and 2008.
```sql=
select name, id, course_id
from takes natural join student
where "year" between 2005 and 2008
group by name,id,course_id
having count(*) > 2;
```
<center>
</center>
Example 18: Check whether all the course numbers are indeed unique.
```sql=
select course_id as not_UNIQUE, count(*)
from course
group by course_id
having count(*) > 1;
```
<center>
</center>
Example 19: Show total credits earned by each student in years between 2003 and 2008, in descending order.
```sql=
select id, name, sum(credits)
from takes natural join course natural join student
where year between 2003 and 2008
group by id, name
order by 3 desc;
```
<center>
</center>
###### tags: `Database Systems` `Pre-Lab` `IUG` `Computer Engineering`
<center>End Of Pre-Lab 3</center>
Sign in with Wallet
Connect another wallet