Section 1
Preview this deck
Date
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 (78)
Section 1
(50 cards)
Date
Stores year, month, and day values
WHERE Clause
Used to extract only those records that fulfill a specified criterion.
What can SQL do?
lets you access and manipulate databases. execute queries against a database can retrieve data from a database 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
Sum Function
returns the sum of values of the selected column.
AND/OR Operators
used to filter records based on more than one condition.
Timestamp
Stores year, month, day, hour, minute, and second values (combines date and time objects)
SELECT TOP Clause
is used to specify the number or percentage of records to return. Returning a large number of records can impact performance. Note: Not all database systems support this clause.
Aliases (AS)
used to give a database table, or a column in a table, a temporary name.
Real
Approximate numerical, mantissa precision 7
UNION
used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement 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.
BETWEEN operator
selects values within a range. The values can be numbers, text, or dates.
Now Function
returns the current system date and time. SELECT NOW() FROM table_name;
Count Function
returns the number of values (NULL values will not be counted) SELECT COUNT(column_name) FROM table_name;
INSERT INTO Statement
used to insert new records in a table
IN Clause
allows you to specify multiple values in a WHERE clause. SELECT * FROM Customers WHERE City IN ('Paris','London');
Lower Case Function
The LCASE() function converts the value of a field to lowercase. SELECT LOWER(column_name) FROM table_name;
Intersect
Use this keyword to return only values that are in the first query AND also in the second query. Same as INNER JOIN
What is CRUD?
Create, Read/Retrieve (Select), Update and Delete. The four basic functions of persistent storage.
Length Function
returns the length of the value in a text field. SELECT LENGTH(column_name) FROM table_name;
SELECT INTO
selects data from one table and inserts it into a new table.
UPDATE Statement
used to update existing records in a table. most include WHERE clause, or all records will be updated!
Group By Function
used in conjunction with the aggregate functions to group the result-set by one or more columns. SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
What is SQL stand for?
SQL stands for Structured Query Language.
Binary Object syntax
Binary string. Fixed-length n Syntax: Blob Text Fixed Length Syntax: Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB)
Upper Case Function
The UCASE() function converts the value of a field to uppercase. SELECT UPPER(column_name) FROM table_name;
SELECT Statement
is used to select data from a database.
Array
A set-length and ordered collection of elements
Time
Stores hour, minute, and second values time
Round Function
used to round a numeric field to the number of decimals specified. NOTE: Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12. SELECT ROUND(column_name,decimals) FROM table_name;
LEFT JOIN
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.
DELETE Statement
used to delete rows in a table. If you omit the WHERE clause, all records will be deleted!
Float
Approximate numerical, mantissa precision 16
Last Function
returns the last value of the selected column. Only in MS Access SELECT LAST(column_name) FROM table_name;
Average Function
returns the average value of a numeric column.
SQL Function types
Aggregate functions Scalar Functions.
INNER JOIN
selects all rows from both tables as long as there is a match between the columns in both tables.
Having Function
this function was added to SQL because the WHERE keyword could not be used with aggregate functions. SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;
JOIN
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
Mid Function
is used to extract characters from a text field. SELECT MID(column_name,start,length) AS some_name FROM table_name;
What goes at the end of each SQL statement?
Semicolon is the standard way to separate each SQL statement in database systems
FULL OUTER JOIN
returns all rows from the left table and from right tables
RIGHT JOIN
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.
First Function
returns the first value of the selected column. Only in MS Access SELECT FIRST(column_name) FROM table_name;
LIKE Operator
used to search for a specified pattern in a column. SELECT * FROM Customers WHERE City LIKE 's%';
Min Function
returns the smallest value of the selected column. SELECT MIN(column_name) FROM table_name;
ORDER BY Keyword
used to sort the result-set by one or more columns. it sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
Max Function
returns the largest value of the selected column. SELECT MAX(column_name) FROM table_name;
DISTINCT Statement
is used in a table, where a column may contain many duplicate values; and you only want to list the different values.
What is RDBMS and what are some examples?
it is the basis for SQL, and for all modern database systems. The data in is stored in database objects called tables.
Are SQL queries case sensitive?
no they are NOT case sensitive
Section 2
(28 cards)