SQL Basics Flashcards
What is a table?
a collection of related data entries and it consists of rows and columns
data is stored in database objects called?
tables
every table is broken up into smaller entities called?
fields
What is a field?
a column in a table designed to maintain specific information about every record in the table
What is a record?
a row / each individual entry that exists in a table
CRUD operations
CREATE
READ
UPDATE
DELETE
which SQL clause extracts data from a database
SELECT
which SQL clause updates data in a database
UPDATE
which SQL clause deletes data in a database
DELETE
which sql clause inserts new data into a database
INSERT INTO
which sql clause creates a new database
CREATE DATABASE
which sql clause modifies a database
ALTER DATABASE
which sql clause creates a new table
CREATE TABLE
which sql clause modifies a table
ALTER TABLE
which sql clause deletes a table
DROP TABLE
which sql clause creates an index (search key)
CREATE INDEX
which sql clause deletes an index
DROP INDEX
which sql statement returns only distinct (different) values
SELECT DISTINCT
how can we return the number of different countries (example)
SELECT COUNT(DISTINCT Country)
how do we filter records
with the WHERE clause and extracts only records that fulfill a specified condition
is the WHERE clause only used in SELECT statements?
NO! Also used in UPDATE, DELETE, etc.
what clause is used to specify between a certain range
BETWEEN
which clause is used to search for a pattern
LIKE
operator not equal
<>
how do we sort results in ASC or DESC order
ORDER BY
ASC is default
what operator displays a record if all conditions are TRUE
AND
what operator displays a record if any of the conditions are TRUE
OR
what operator is used to give the opposite result called the negative result
NOT
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …)
a field with a NULL value is a field with __ __
no value
how to check for null values with operators
IS NULL | IS NOT NULL
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
if you omit the WHERE clause, all records will be updated
DELETE FROM table_name WHERE condition;
where clause specifies which record(s) should be deleted and if you forget this clause all records in the table will be deleted!
what if we want to specify number of records to return
MySQL supports LIMIT clause
some support SELECT TOP # * FROM table_name;
Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM (after FROM or ORDER BY)
what do aggregate functions do?
a function that performs a calculation on a set of values and returns a single value
what SQL clause are aggregate functions often used with
GROUP BY (aggegate functions can be used to return a single value for each group)
GROUP BY clause does what?
splits the result-set into groups of values
what aggregate function does NOT ignore null values
COUNT()
SQL aggregate function examples
MIN, MAX, COUNT, SUM, AVG
what does MIN() return
smallest value of selected column
what does MAX() return
largest value of selected column
if you specify a column name instead of * with COUNT(), will NULL values be counted?
NO
what does SUM() return?
the total sum of a numeric column
can also use SUM() as an expression
SELECT SUM(Quantity * 10)
FROM OrderDetails;
what does AVG() return?
the average value of the numeric column
NULL values are ignored
concatenate columns
SELECT CustomerName, Address + ‘, ‘ + PostalCode + ‘ ‘ + City + ‘, ‘ + Country AS Address
FROM Customers;
concatenate columns in MySQL
SELECT CustomerName, CONCAT(Address, ‘, ‘, PostalCode, ‘, ‘, City, ‘, ‘, Country) AS Address
FROM Customers;
concatenate columns in Oracle
SELECT CustomerName, (Address || ‘, ‘ || PostalCode || ‘ ‘ || City || ‘, ‘ || Country) AS Address
FROM Customers;
we want to combine rows from two or more tables based on a related column between them
use JOINs
different types of SQL Joins
(INNER) JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN
FULL (OUTER) JOIN
(INNER) JOIN
returns records that have matching values in both tables
LEFT (OUTER) JOIN
returns all records from left table, and matched records from right table
RIGHT (OUTER) JOIN
returns all records from right table, and matched records from left table
FULL (OUTER) JOIN
returns all matching records from both tables where the other table matches or not
SQL Self Join
a regular join but the table is joined with itself
what operator can we use to combine the result-set of two or more SELECT statements
UNION
but every SELECT statement within UNION must have same number of columns and similar data types and must be in same order
what is different between UNION and UNION ALL?
UNION ALL allows for duplicate values
what if we want to filter rows after they have been aggregated?
use HAVING clause
EXISTS operator
test for existence of any record in a subquery
ANY and ALL operators allow you to perform a comparison between a ___ ___ value and a ____ of other values
single column, range
ANY Operator returns
boolean value as result
so TRUE if ANY of subquery values meet the condition
ALL operator returns
a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
SELECT INTO statement does what
copies data from one table into a new table
SELECT *
INTO newTable [IN externalDb]
FROM oldTable
WHERE condition;
SQL case expression goes through conditions and returns a value when first condition is met
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END AS columnAlias;
MySQL -> IFNULL() and COALESCE()
allows us to return an alternative value if an expression is NULL
what is a stored procedure?
a prepared SQL code that we can save so code can be reused over and over again
how do we execute a stored procedure?
call it
EXEC procedure_name;
can we pass parameters to a stored procedure so that stored procedure can act based on parameter value(s) that is passed?
yes
CREATE PROCEDURE procedure_name
AS sql_statement
GO;
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
stored procedure example with one parameter
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;
EXEC SelectAllCustomers @City = ‘London’;
SQL comments
– single line comment
/* Multi-
line
comment */
