Sql

Front 1

Define SQL

Back 1

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)

Front 2

Name the common flavours

Back 2

-Oracle

-MySQL

-SQLite

-PostgresSQL

-MSQL (Microsoft)

-Microsoft Access

Most implementations do not implement the entire standards but they all implement the basics

Front 3

Basic form of an SQL Query

Back 3

SELECT column_name

FROM table_name

WHERE condition_is_true

Front 4

Define each of the commands in the basic query structure

Back 4

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

Front 5

How to access data from a relational database

Back 5

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

Front 6

SQL Case sensitivity, state for commands and data

Back 6

-SQL commands are not case sensitive, however conventionally all capitals

-Data is case sensitive

Front 7

Convention for writing queries

Back 7

-SQL commands: all capitals (SELECT)

-Table names: start with capital (Student)

-Column names: all lower-case (fname)

Front 8

What must SQL queries start with, and what must they include and what can they potentially include

Back 8

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

Front 9

Discuss SELECT DISTINCT

Back 9

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

Front 10

Define all operators for the WHERE clause

Back 10

>: 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

Front 11

Give examples using IN for string and integers and the logical operator it uses

Back 11

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

Front 12

Give examples of using BETWEEN for strings and integers and state the range and logical operator

Back 12

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'

Front 13

What do NOT IN and NOT BETWEEN do

Back 13

They consider the entries outside of the ranges stated

Front 14

What does IS NULL do and write a command with it

Back 14

IS NULL considers columns which have no value, note this is different than an empty string or 0.

WHERE address IS NULL;

Front 15

How must we always close of a query

Back 15

With ;

Front 16

What does IS NOT NULL do

Back 16

This considers columns which are not null, note it would also return empty strings or 0

Front 17

Define an AND quality

Back 17

Means two conditions must be true

Front 18

Define an OR qualtiy

Back 18

Either of the two conditions must be true

Front 19

Define ORDER BY

Back 19

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

Front 20

Define aggregate functions

Back 20

Return a single value, calculated from values in a column

Front 21

General syntak of a function query and useful aggregate functions

Back 21

SELECT FUNCTION_NAME(col_name) FROM Table_name

-AVG()

-MAX()

-MIN()

-SUM()

-COUNT()

Front 22

State what these three queries return

SELECT COUNT (*) FROM Employee

SELECT COUNT (salary) FROM Employee

SELECT COUNT (DISTINCT salary) FROM Employee

Back 22

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

Front 23

Describe what GROUP BY does, with an examples

Back 23

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

Front 24

If listing columns in a query, how must they be seperated

Back 24

With commas