Section 1
Preview this deck
CREATE TABLE
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (81)
Section 1
(50 cards)
CREATE TABLE
-used to create a table within a database -both column names and data types can be specified -data is then entered using INSERT INTO Syntax: CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, )
TRUNCATE
-removes data inside a table, but not the table itself Syntax: TRUNCATE TABLE table_name
DML
-DML stands for Data Manipulation Language -The query and update commands of SQL: SELECT UPDATE DELETE INSERT INTO
SYSDATETIME()
-
--
-comments -used to comment something out
FULL JOIN
-returns rows when there is a match in one of the tables, even if there are no matches in some columns -rows without matching information are still displayed Syntax: SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
JOIN
-returns rows where there is at least 1 match in both tables -rows without matching information are not displayed -same as INNER JOIN Syntax: SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
DISTINCT
-used in combination with SELECT to return only distinct (or different/non-duplicate values) in a table Syntax: SELECT DISTINCT column_name(s) FROM table_name
LIKE
-operator used in a WHERE clause to search for a specified pattern in a column -Uses wildcards to control this -Can also specifiy NOT LIKE to display everything that doesn't have the specified criteria Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
UPDATE
-most likely won't use this at ACOM -used to update records in a table -relies on SET command -DO NOT forget to use a WHERE clause to specify which record or records to update. If you do, ALL records will be updated! Syntax: UPDATE table_name SET column1=value1, column2=value2,... WHERE some_column=some_value
BETWEEN
-operator used in a WHERE clause to select a range of data between 2 values -values used don't need to be listed in order, for example, you could list S as the starting and L as the secondary Example: WHERE City BETWEEN 'Berlin' AND 'London'
WHERE
-used to extract information that meets a certain criteria -Operators allowed in a WHERE clause: = - equal <> - not equal ( in some versions of SQL, can also be != ) > - greater than < - less than >= - greater than or equal <= - less than or equal BETWEEN - between an inclusive range LIKE - search for a pattern IN - if you know the exact value you want to return for at least one of the columns -operators are used in combination with single (or double) quotes for alpha-characters. Numbers do not use quotes. Example 1: WHERE FirstName='Gibson' Example 2: WHERE HouseNumber<>569
OR
-operator used to display records if either the first or second conditions are true -can be combined with AND by using parenthesis to form complex expressions Example 1: SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola' Example 2: SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')
SELECT INTO
-most likely won't use at ACOM -selects data from one table and inserts it into another -most often used to create backups of tables -columns to copy can be specified under SELECT -can use IN clause to copy the table into another database -can also use WHERE clause to select information that meets specified criteria -using JOINs also allow you to copy data from more than one table Example 1: SELECT * INTO Persons_Backup FROM Persons Example 2 - using IN: SELECT * INTO Persons_Backup IN 'Backup.mdb' FROM Persons Example 3 - specifying what columns to copy from: SELECT LastName,FirstName INTO Persons_Backup FROM Persons Example 4 - using WHERE: SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes' Example 5 - using JOINs: SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id
TOP
-clause used to specify the number of returns returned -can be designated by number or results, or percentage -percent must be written out, as in Example 2 Example 1: SELECT TOP 10 * FROM Persons Example 2: SELECT TOP 25 PERCENT * FROM Persons
FOREIGN KEY
-used to point to a PRIMARY KEY in another table -used to prevent actions that could destroy links between tables -also prevents invalid data from being inserted into the FOREIGN KEY column, because it has to be on of the values contained in the table it points to -relies on REFERENCES to specify the PRIMARY KEY in the other table Example 1: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) Example 2: ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
SQL Alias
-alias can be given to table or column names -can be anything, but usually used to shorten names -uses AS to designate the alias -tables names are given aliases as part of FROM -columns are given aliases as part of SELECT Example: Table: FROM Customer AS C Column: SELECT LastName AS LN, Address AS Addr
SQL
-SQL stands for Structured Query Language -Not case sensitive -Divided into two parts: Data Manipulation Language (DML) and Data Definition Language (DDL)
DDL
-DDL stands for Data Definition Language -Part of SQL that permits database tables to be created or deleted -Also defines indexes (keys), specify links between tables, or impose constraints between tables -Most important DDL statements in SQL are: CREATE DATABASE ALTER DATABASE CREATE TABLE DROP TABLE CREATE INDEX DROP INDEX
AUTO_INCREMENT
-allows you to automatically create the value of the primary key field each time a new record is inserted -by default, starts at 1 and increments by 1, but can be adjust to start and increment at different numbers -MS SQL uses IDENTITY to do the same Example: CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) Example 2: .. P_Id int PRIMARY KEY IDENTITY(10,5) ..
CREATE OR REPLACE VIEW
-similar to create view, but if a view of the same name exists, it is overwritten. -you can only replace a view with a new query that generates the identical set of columns Syntax: CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
RIGHT JOIN
-returns all rows from the right table, even if there are no matches in the left table -rows without matching information are still displayed -some databases call this RIGHT OUTER JOIN Syntax: SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
ALTER TABLE
-used to add, delete, or modify columns in an existing table Syntax - Adding a column: ALTER TABLE table_name ADD column_name datatype Syntax - deleting a column: ALTER TABLE table_name DROP COLUMN column_name Syntax - changing the data type in a column: ALTER TABLE table_name ALTER COLUMN column_name datatype
RDBMS
-RDBMS stands for Relational Database Management System
CHECK
-used to limit the value range that can be placed in a column or columns -sort of like a "required" in forms, checking to see the data meets specific requirements Example 1: CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) Example 2: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') )
AND
-operator used to display records where both the first and second conditions are true -can be combined with OR by using parenthesis to form complex expressions Example 1: SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson' Example 2: SELECT * FROM Persons WHERE LastName='Svendson' AND (FirstName='Tove' OR FirstName='Ola')
SQL Constraints
-used to limit the type of data that can go into a table -can be specified as the table is created (with CREATE TABLE and CONSTRAINT) or after (with ALTER TABLE and ADD CONSTRAINT) -removed using DROP CONSTRAINT Commonly used constraints: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT
UNION
-operator used to combine the result-set of 2 or more SELECT statements -each SELECT statement within the UNION must have the same number of columns listed, with similar data types, and listed in the same order. -resulting column names are always equal to the column names listed in the first SELECT statement -only selects distinct values, unless UNION ALL is also used to show duplicate values Example: SELECT Surname FROM Norway UNION SELECT LastName FROM USA
*
-used to denote everything in a table, rather than specifying certain columns when using SELECT
INSERT INTO
-used to insert a new row in a table -can be written in 2 ways: specifying column names, or not specifying column names Syntax - Columns specified INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,...) •information inserted goes into the columns specified, leaving others blank Syntax - No column specified INSERT INTO table_name VALUES (value1, value2,...) •information inserted goes into columns in order from left to right
SYSDATETIMEOFFSET()
...
PRIMARY KEY
-uniquely identifies each record in a database table -must contain unique values -cannot contain NULL values -each table should have a primary key, but can only have 1 primary key -can span across multiple columns (meaning those columns together are the primary key) - -removed using DROP CONSTRAINT
SQL Joins
-used to query data from 2 or more tables based on a relationship between certain columns -controlled using ON to compare the columns that share the same info Join Types: JOIN (INNER JOIN) LEFT JOIN RIGHT JOIN FULL JOIN
`
-backtack (not single quote) -used to control things where spacing is necessary, but you don't want displayed with single or double quotes -doesn't apply to T-SQL, or at least how we use it Example: SELECT Customer AS `Cu StOmEr` -column is displayed named as Cu StOmEr rather than "Cu StOmEr"
NOT NULL
-by default, a table can hold NULL values -the NOT NULL constraint enforces a column to NOT accept NULL values, or always contain a value. Example: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
ORDER BY
-used to sort the result set by a specified column -sorts by ascending by default, but can be specified to sort in ascending or descending order with ASC or DESC keywords Syntax: SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC
CREATE INDEX
-used to create indexes in tables -indexes allow the database application to find data fast without reading the whole table -users can't see indexes -updating tables with indexes takes longer than updating those without, so indexes should only be created on columns and tables that will be frequently searched against -can be combined with UNIQUE to control duplicate values Syntax: CREATE INDEX index_name ON table_name (column_name)
DEFAULT
-used to enter a default value into a column -default value is added to all new records if no other value is specified -can also be used to insert system values by using functions like GETDATE() Example: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' )
LEFT JOIN
-returns all rows from the left table, even if there are no matches in the right table -rows without matching information are still displayed -some databases call this LEFT OUTER JOIN Syntax: SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
SELECT
-used to select data from a database -result is stored in a result table, or result-set -columns displayed in order they are listed in SELECT
DROP
-allows you to delete or remove many items, including: INDEX TABLE DATABASE CONSTRAINT COLUMN VIEW Syntax: DROP INDEX table_name.index_name
DELETE
-used to delete records from a table -do not forget to use a WHERE clause to specify which record or records to delete. If you do, ALL records will be deleted, and cannot be undone -all rows in a table can be deleted without deleting the table. This means that the table structure, attributes, and indexes will be intact. Syntax: DELETE FROM table_name WHERE some_column=some_value
IN
-allows you to specify multiple values in a WHERE clause -values entered inside of parenthesis, separated by commas Example: WHERE LastName IN ('Gibson','Jones')
CREATE VIEW
-creates a view based on the result set of the statement Example: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
CREATE DATABASE
-used to create a database -tables are then created/added using CREATE TABLE Syntax: CREATE DATABASE database_name
DROP VIEW
-deletes a view Syntax: DROP VIEW view_name
(nolock)
-always used when selecting a table so that others can still access and write to while you are accessing it. -do not forget to put this in
SQL Wildcards
% - substitute for zero or more characters. can used before or after [charlist] _ - substitute for one character. can be used before or after [charlist] [charlist] - any single character in the charlist [^charlist] or [!charlist] - any single character NOT in the charlist
UNIQUE
-uniquely identifies each record in a database table -UNIQUE and PRIMARY KEY both provide a guarantee for uniqueness for a column or set of columns -can have many UNIQUE constraints per table -removed using DROP
SQL Views
-a view is a virtual table based on the result set of an SQL statement -similar to aliases, but it's creating an entire aliased table -contains rows and columns, just like a real table. -fields in a view are fields from one or more real tables in the database. -always shows up to date data, as the view is created on the fly each time a user queries it. CREATE VIEW CREATE OR REPLACE VIEW DROP VIEW
Section 2
(31 cards)