Unit 5 - SQL (Part 1) Flashcards
(26 cards)
What is SQL?
it’s a query language (standard query language)
used to communicate with a RDBMS (relational database management system) = mySQL, Oracle, Postgres
1st step in SQL
CREATE DATABASE database_name;
DROP DATABASE database_name; (to delete)
Build a SQL table from the following schema:
Students (Sid, name, login, age, gpa, DOB)
CREATE TABLE Students
(Sid VARCHAR (20),
name VARCHAR (30),
login VARCHAR (20),
age INTEGER,
gpa: DECIMAL (2, 1),
DOB: DATE
);
What else can you add to a table when creating it (optional)?
Constraints and checks
What are some examples of constraints that you can use?
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
);
What is the syntax of ‘check’ and what are some examples of checks you can use?
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)
);
How do you add a primary key to a table?
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
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.
CREATE TABLE Boat(
Bid VARCHAR(20),
…
SailorID VARCHAR(20)
PRIMARY KEY (Bid)
FOREIGN KEY (SailorID) REFERENCES Sailor(Sid)
After creating a table, how do you insert records?
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)
How do you update value of attributes after adding them?
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
How to delete records from a table
DELETE
FROM table_name;
^ deletes all records
DELETE
FROM table_name;
WHERE condition or conditions;
^ specify which records to apply the delete query
What’s the difference between DELETE, TRUNCATE and DROP?
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
How do you update columns?
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name;
What’s the basic structure of an SQL query? What is the MINIMUM structure of a query?
SELECT attributes <– project operator in relational algebra
FROM relations <– which table/tables to pull from
a query MUST contain SELECT and FROM
How to display ALL columns/ attributes in the result?
Asterisk (aka wildcard)
SELECT *
FROM Students;
This will show all rows (attributes) and columns (records/tuples)
How do you display only certain records from a table?
SELECT *
FROM Students;
WHERE condition(s);
^ this will same as ‘select’ / sigma operator
Can use logical operators (AND, NOT, OR)
How to check for records with null values?
SELECT attribute(s)
FROM table(s)
WHERE attribute is NULL;
can also use (IS NOT NULL;)
Does SQL allow duplicate records?
Yes
(unlike relational algebra)
How do you eliminate duplicate records from result in SQL?
‘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)
How you sort the data? (based on attribute(s))
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
How to search for a specific pattern in a value? For example, find all names that DON’T start with an ‘A’
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%’;
What’s the SQL query for, find all customers with names that have ‘r’ in the second position?
SELECT *
FROM Customers
WHERE name LIKE ‘_r%’;
How can you show the names of the top 100 employees whose salary is >10,000?
SELECT name
From Employees
WHERE Salary > 10,000
LIMIT 100;
Show me all students whose GPAs are between 2.5 and 3.5, inclusive
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)
