Unit 5 - SQL (Part 1)

Question 1

What is SQL?

Answer 1

it’s a query language (standard query language)
used to communicate with a RDBMS (relational database management system) = mySQL, Oracle, Postgres

Question 2

1st step in SQL

Answer 2

CREATE DATABASE database_name;
DROP DATABASE database_name; (to delete)

Question 3

Build a SQL table from the following schema:
Students (Sid, name, login, age, gpa, DOB)

Answer 3

CREATE TABLE Students
(Sid VARCHAR (20),
name VARCHAR (30),
login VARCHAR (20),
age INTEGER,
gpa: DECIMAL (2, 1),
DOB: DATE
);

Question 4

What else can you add to a table when creating it (optional)?

Answer 4

Constraints and checks

Question 5

What are some examples of constraints that you can use?

Answer 5

NOT NULL and Default value

e.g.,
CREATE TABLE Students (
Sid VARCHAR (20) NOT NULL,
name VARCHAR (30) NOT NULL,
login VARCHAR (20) DEFAULT ‘no email’,
age INTEGER, DEFAULT 18
);

Question 6

What is the syntax of ‘check’ and what are some examples of checks you can use?

Answer 6

Syntax is CHECK (Boolean_expression)

CREATE TABLE Students(
Sid VARCHAR (20) NOT NULL,
name VARCHAR (30) NOT NULL,
age INTEGER DEFAULT 18,
gpa: DECIMAL (2, 1),
CHECK (Age >= 18),
CHECK (gpa >= 0.0 and gpa <=4.0)
);

Question 7

How do you add a primary key to a table?

Answer 7

Each table MUST have a primary key, add in ‘create table’ –

CREATE TABLE Students(
Sid VARCHAR (20) NOT NULL,
name VARCHAR (30) NOT NULL,
age INTEGER DEFAULT 18,
gpa: DECIMAL (2, 1),
PRIMARY KEY (Sid)
);

Can also have as a LIST of key attributes

Question 8

How do you reference a foreign key? e.g., create table Boat, which has a primary key ‘Bid’ and another key ‘Sailor ID’ which references ‘Sid’ in table Sailor.

Answer 8

CREATE TABLE Boat(
Bid VARCHAR(20),
…
SailorID VARCHAR(20)
PRIMARY KEY (Bid)
FOREIGN KEY (SailorID) REFERENCES Sailor(Sid)

Question 9

After creating a table, how do you insert records?

Answer 9

Two ways:

INSERT
INTO table_name
VALUES (value 1, value 2,…, value n);
^^ values are in the same order as headers (can add multiple values, just separate paranetheses with comma)

INSERT
INTO table_name (Attribute 1, Attribute 2,… Attribute n)
VALUES (value 1, value 2,… value n);
^ you can specify which attributes you’re adding to; attributes not specified will have default value or NULL (if allowed)

Question 10

How do you update value of attributes after adding them?

Answer 10

UPDATE table_name
SET attribute 1 = newvalue1, attribute2= newvalue2,…
WHERE condition;
^ where is optional, tells the system WHICH RECORDS to apply the update query; if no condition, all records will be updated

Question 11

How to delete records from a table

Answer 11

DELETE
FROM table_name;
^ deletes all records

DELETE
FROM table_name;
WHERE condition or conditions;
^ specify which records to apply the delete query

Question 12

What’s the difference between DELETE, TRUNCATE and DROP?

Answer 12

TRUNCATE TABLE table name;
deletes all records from the table (but leaves the table itself)

DELETE
FROM table_name;
also deletes all records from the table (but leaves the table)

DROP TABLE table_name;
deletes the table itself

Question 13

How do you update columns?

Answer 13

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

Question 14

What’s the basic structure of an SQL query? What is the MINIMUM structure of a query?

Answer 14

SELECT attributes <– project operator in relational algebra
FROM relations <– which table/tables to pull from

a query MUST contain SELECT and FROM

Question 15

How to display ALL columns/ attributes in the result?

Answer 15

Asterisk (aka wildcard)
SELECT *
FROM Students;

This will show all rows (attributes) and columns (records/tuples)

Question 16

How do you display only certain records from a table?

Answer 16

SELECT *
FROM Students;
WHERE condition(s);
^ this will same as ‘select’ / sigma operator
Can use logical operators (AND, NOT, OR)

Question 17

How to check for records with null values?

Answer 17

SELECT attribute(s)
FROM table(s)
WHERE attribute is NULL;

can also use (IS NOT NULL;)

Question 18

Does SQL allow duplicate records?

Answer 18

Yes
(unlike relational algebra)

Question 19

How do you eliminate duplicate records from result in SQL?

Answer 19

‘DISTINCT’ operator:

SELECT DISTINCT attributes
FROM relations
WHERE conditions;

SELECT DISTINCT name
^ this will only show each name once

SELECT DISTINCT name, gpa
^ this will only each each name+gpa combo once (so a name can appear twice if they have different gpas)

Question 20

How you sort the data? (based on attribute(s))

Answer 20

SELECT DISTINCT attribute(s)
FROM relation(s)
WHERE condition(s)
ORDER BY attribute(s)[ASC-DESC];

For example,
ORDER BY name ASC, GPA DESC;
^^ This will order the result by name in ascending order
If there are duplicate names, they’ll be ordered by GPA in descending order

Question 21

How to search for a specific pattern in a value? For example, find all names that DON’T start with an ‘A’

Answer 21

Use the LIKE opeartor in the WHERE clause:

WHERE column_N LIKE pattern;

Two main wildcards to use with the ‘LIKE’ operator are % and _
% represents zero, one, or multiple characters
_ represents any single character

Find all names that start with an ‘A’ =
WHERE name LIKE NOT ‘a%’;

Question 22

What’s the SQL query for, find all customers with names that have ‘r’ in the second position?

Answer 22

SELECT *
FROM Customers
WHERE name LIKE ‘_r%’;

Question 23

How can you show the names of the top 100 employees whose salary is >10,000?

Answer 23

SELECT name
From Employees
WHERE Salary > 10,000
LIMIT 100;

Question 24

Show me all students whose GPAs are between 2.5 and 3.5, inclusive

Answer 24

Use the BETWEEN operator in WHERE statement:

SELECT *
From Students
WHERE GPA BETWEEN 2.5 AND 3.5;

BETWEEN Operator is inclusive (first and last values included)

Question 25

How do search for something that’s a member of a set? e.g., find all employees who’s position is either manager or supervisor

Answer 25

The set here is (‘Manager’, ‘Supervisor’) so the search is,

SELECT name
FROM employees
WHERE position IN (‘Manager’, ‘Supervisor’)

This will pull all names of employees who’s positions are in this set
Can also use NOT IN

Question 26

After creating a database, before adding tables, what keyword must you use to point to the correct database?

Answer 26

USE database_name;

Otherwise, RDBMS won’t know which database to add tables to.