Section 1
Preview this deck
WHERE Clause
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 (63)
Section 1
(50 cards)
WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion. Syntax: SELECT column_name,column_name FROM table_name WHERE column_name operator value;
BETWEEN operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Data Type: Date
Stores year, month, and day values date
DISTINCT Statement
In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. Syntax: SELECT DISTINCT column_name,column_name FROM table_name;
DROP
Indexes, tables, and databases can easily be deleted/removed with the DROP statement. DROP INDEX table_name.index_name DROP DATABASE database_name
CREATE TABLE
The CREATE TABLE statement is used to create a table in a database. Tables are organized into rows and columns; and each table must have a name. CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );
Min Function
The MIN() function returns the smallest value of the selected column. SELECT MIN(column_name) FROM table_name;
Data Type: Real
Approximate numerical, mantissa precision 7 real
SELECT INTO
The SELECT INTO statement selects data from one table and inserts it into a new table. SELECT * INTO newtable [IN externaldb] FROM table1; SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
Last Function
The LAST() function returns the last value of the selected column. Only in MS Access SELECT LAST(column_name) FROM table_name;
Data Type: Variable Character
Character string. Variable length. Maximum length n varchar(64)
ALTER
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype ALTER TABLE table_name DROP COLUMN column_name
INSERT INTO
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. INSERT INTO table2 SELECT * FROM table1; INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;
Data Type: Timestampe
Stores year, month, day, hour, minute, and second values timestamp
First Function
The FIRST() function returns the first value of the selected column. Only in MS Access SELECT FIRST(column_name) FROM table_name;
Data Type: Aray
A set-length and ordered collection of elements
UNION
The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
Data Type: Binary Object
Binary string. Fixed-length n Syntax: Blob Text Fixed Length Syntax: Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB)
Max Function
The MAX() function returns the largest value of the selected column. SELECT MAX(column_name) FROM table_name;
UPDATE Statement
The UPDATE statement is used to update existing records in a table. UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; Notice the WHERE clause in the SQL UPDATE statement! The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword. SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;
DELETE Statement
The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column=some_value; Notice the WHERE clause in the SQL DELETE statement! The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table. INSERT INTO table_name VALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
Count Function
The COUNT() function returns the number of rows that matches a specified criteria. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column: SELECT COUNT(column_name) FROM table_name;
SQL Functions
SQL has many built-in functions for performing calculations on data. Aggregate functions and Scalar Functions.
What goes at the end of each SQL statement?
Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
SELECT Statement
The SELECT statement is used to select data from a database. Syntax: SELECT column_name, column_name FROM table_name; SELECT * FROM table_name;
FOREIGN KEY
A FOREIGN KEY in one table points to a PRIMARY KEY in another table. CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )
What is SQL?
SQL stands for Structured Query Language. SQL lets you access and manipulate databases.
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key. CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
Views
A view is a virtual table.
UNIQUE Constraint
The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );
CREATE DATABASE
The CREATE DATABASE statement is used to create a database. CREATE DATABASE dbname;
IN Clause
The IN operator allows you to specify multiple values in a WHERE clause. SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); SELECT * FROM Customers WHERE City IN ('Paris','London');
JOIN
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
What can SQL do?
SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views
Data Type: Decimal
Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal. decimal(6,2)
Data Type: Boolean
Stores TRUE or FALSE values boolean
AND/OR Operators
The AND & OR operators are used to filter records based on more than one condition. Syntax: SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
Are SQL queries case sensitive?
SQL keywords are NOT case sensitive: select is the same as SELECT
Average Function
The AVG() function returns the average value of a numeric column. SELECT AVG(column_name) FROM table_name
Data Type: Time
Stores hour, minute, and second values time
Data Type: Integer
Integer numerical (no decimal). Precision p int
INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name; SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;
Data Type: Float
Approximate numerical, mantissa precision 16 float
LIKE Operator
The LIKE operator is used to search for a specified pattern in a column. SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; SELECT * FROM Customers WHERE City LIKE 's%';
What is RDBMS and what are some examples?
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;
Section 2
(13 cards)