Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
24 Cards in this Set
- Front
- Back
Define SQL |
SQL: (Structured Query Language): is a standard language for accessing and manipulating information in a relational database. The language consists of SQL commands and is an ANSI (American National Standards Institute) Standard, but there are slightly different flavours (versions) |
|
Name the common flavours |
-Oracle -MySQL -SQLite -PostgresSQL -MSQL (Microsoft) -Microsoft Access Most implementations do not implement the entire standards but they all implement the basics |
|
Basic form of an SQL Query |
SELECT column_name FROM table_name WHERE condition_is_true |
|
Define each of the commands in the basic query structure |
SELECT: Get the data you want, normally column names, sometimes a function of a column. FROM: Find the table WHERE: find the rows which satisfy this condition, can be dropped |
|
How to access data from a relational database |
Use SQL queries which are made up of commands such as SELECT, FROM, WHERE, etc. These can then potentially return a result from the database |
|
SQL Case sensitivity, state for commands and data |
-SQL commands are not case sensitive, however conventionally all capitals -Data is case sensitive |
|
Convention for writing queries |
-SQL commands: all capitals (SELECT) -Table names: start with capital (Student) -Column names: all lower-case (fname) |
|
What must SQL queries start with, and what must they include and what can they potentially include |
All SQL queries start with a keyword (SELECT) this tells the DBMS what type of operation is going to be executed. They must include the table which to operate on They can potentially include columns which are affected by the operation |
|
Discuss SELECT DISTINCT |
Sometimes results are repeated, so we use SELECT DISTINCT if we only want distinct column values. This command must always be followed with a col_name |
|
Define all operators for the WHERE clause |
>: Greater than <: Less than <>: Not equal >=: Greater than or Equal to <=: Less than or Equal to BETWEEN: Between an inclusive range IN: In a specified set of values NOT NULL: The column has a value IS NULL: The column has no value |
|
Give examples using IN for string and integers and the logical operator it uses |
String: WHERE position IN 'Manager' OR 'Supervisor' Considers case of both manager and supervisor Integer: WHERE studentID IN (1,3,7) Considers case of studentID with 1,3 or 7 IN is based on the OR operator |
|
Give examples of using BETWEEN for strings and integers and state the range and logical operator |
BETWEEN range is inclusive and is based on the AND operator Integer: WHERE salary BETWEEN 5000 AND 10000 String WHERE recipes BETWEEN 'apples' AND 'pears' |
|
What do NOT IN and NOT BETWEEN do |
They consider the entries outside of the ranges stated |
|
What does IS NULL do and write a command with it |
IS NULL considers columns which have no value, note this is different than an empty string or 0. WHERE address IS NULL; |
|
How must we always close of a query |
With ; |
|
What does IS NOT NULL do
|
This considers columns which are not null, note it would also return empty strings or 0 |
|
Define an AND quality |
Means two conditions must be true |
|
Define an OR qualtiy |
Either of the two conditions must be true |
|
Define ORDER BY |
Use this command to order the results which are returned by the query Example: ORDER BY fname ASC ASC: ascending order (default) DSC: descending order Note; this command must always be the last in the query |
|
Define aggregate functions |
Return a single value, calculated from values in a column |
|
General syntak of a function query and useful aggregate functions |
SELECT FUNCTION_NAME(col_name) FROM Table_name -AVG() -MAX() -MIN() -SUM() -COUNT() |
|
State what these three queries return SELECT COUNT (*) FROM Employee SELECT COUNT (salary) FROM Employee SELECT COUNT (DISTINCT salary) FROM Employee |
1) Returns the number of rows in the table 2) Returns the number of rows in which salary is not null 3) Returns the number of different salaries in the table |
|
Describe what GROUP BY does, with an examples |
Returns values from aggregate functions for distinct groups SELECT COUNT(staffID), school FROM Lecturer GROUP BY school Returns the number of lecturers in each school |
|
If listing columns in a query, how must they be seperated |
With commas |