Basics Flashcards

Question 1

Q

SQL

Answer

A

standard language for storing, manipulating and retrieving data in databases.
Structured Query Language

Question 2

Q

What can SQL do?

Answer

A

EXECUTE query
READ data
INSERT records
UPDATE records
DELETE records
create DATABASES, TABLES, STORED PROCEDURES, VIEWS
set PERMISSIONS on tables, stored procedures and views

Question 3

Q

RDBMS

Answer

A

Relational Database Management System

* stores tables consisting of columns and rows

Question 4

Q

SQL statement/syntax

Answer

A

executable block of sql code
keywords are not case sensitive
semicolon at the end of each statement

Question 5

Q

SELECT

Answer

A

reads data from a database
returns result in a table (result-set)
example:
SELECT * FROM table_name; -> returns all columns from table
SELECT column1, column2 FROM table_name; -> returns specified column(s) from table

Question 6

Q

SELECT DISTINCT

Answer

A

reads distinct/unique values from table
example:
SELECT DISTINCT column1 FROM table_name; -> returns unique values for column1 from table

Question 7

Q

WHERE

Answer

A

filter records by given conditions
can be use for UPDATE and DELETE statements
example:
SELECT column1 FROM table_name WHERE condition; -> returns column1 values meeting the given condition

Question 8

Q

WHERE clause operators

Answer

A

= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Question 9

Q

AND OR NOT operators

Answer

A

AND and OR are used in WHERE clause to filter for additional conditions
NOT is used for returning opposite or not true conditions

example:
SELECT column1 FROM table_name
WHERE condition1 AND condition2…. ; -> return values where all conditions are met

    SELECT column1  FROM table_name 
    WHERE condition1 OR condition2....   ; -> return values where any conditions are met

    SELECT column1  FROM table_name 
    WHERE NOT condition1; -> return values where condition is not met

    SELECT column1  FROM table_name 
    WHERE condition1 AND (condition2 OR condition3) ....   ; -> nested conditions

    SELECT column1  FROM table_name 
    WHERE NOT condition1 AND (NOT condition2 OR condition3)....   ; -> nested condition with not operator

Question 10

Q

ORDER BY

Answer

A

sorts results in ascending (default) or descending order
use keyword ASC for asecnding DESC for descending
example:
SELECT column1, column2 FROM table_name
ORDER BY column1, column2, … ASC(DESC); -> sorts results in ASCending order (or DESCending order)

Question 11

Q

INSERT INTO

Answer

A

example:
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …); -> values 1 goes to column1, value2 to column2, etc

    INSERT INTO table_name
    VALUES (value1, value2, ...); -> values 1 goes to column1, value2 to column2, and so on as per columns order on table

Question 12

Q

NULL

Answer

A

field with no value (saved as NULL)
use IS NULL or IS NOT NULL to test for null values

example:
SELECT column1 FROM table_name
WHERE condition1 IS NULL; -> return rows with null values

    SELECT column1  FROM table_name 
    WHERE condition1 IS NOT NULL; -> return rows without null values

Question 13

Q

UPDATE

Answer

A

use to modify existing records in a table
WHERE clause is used to specify which record to update, if no where clause then all records are updated
where clause can update more than 1 record depending on filter result
example:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition(s); -> modify column values filtered by condition

Question 14

Q

DELETE

Answer

A

remove records from table
WHERE clause to filter record(s) to remove
if no where clause then all records are removed
example:
DELETE FROM table_name
WHERE condition(s); -> remove record(s) filtered by condition
```
    DELETE FROM table_name; -> remove all records
```

Question 15

Q

SELECT number of rows

Answer

A

same as select but limit number of records returned
uses different syntax depending on db: LIMIT, ROWNUM, TOP

example:
SELECT TOP number column_name(s)
FROM table_name
WHERE condition; -> return ‘number’ of rows, used by SQL and MS Access

    SELECT TOP number PERCENT column_name(s)
    FROM table_name
    WHERE condition; -> return percentage of rows, used by SQL and MS Access

          SELECT column_name(s) FROM table_name
          WHERE condition
          LIMIT number; -> return 'number' of rows, used by MySQL

      SELECT TOP number column_name(s)
      FROM table_name
      WHERE ROWNUM <= number; -> return 'number' of rows, used by Oracle

Question 16

Q

aggregate functions

AVG MIN MAX SUM COUNT

Answer

A

returns a value based on function called
most popular functions are: MIN(), MAX(), AVG(), COUNT(), SUM()
NULL values are ignored
example:
SELECT MIN(column_name) as a_different_name
FROM table_name
WHERE condition; -> returns minimum value

Question 17

Q

LIKE

Answer

A

used in WHERE clause to search to specific patterns
uses 2 types of wildcards: % -> to represent 0 to infinite char, _ -> represent single char
use of LIKE:
WHERE column LIKE ‘a%’ -> Finds any values that start with “a”
WHERE column LIKE ‘%a’ -> Finds any values that end with “a”
WHERE column LIKE ‘%or%’ -> Finds any values that have “or” in any position
WHERE column LIKE ‘r%’ -> Finds any values that have “r” in the second position
WHERE column LIKE ‘a%’ -> Finds any values that start with “a” and are at least 2 characters in length
WHERE column LIKE ‘a__%’ -> Finds any values that start with “a” and are at least 3 characters in length
WHERE column LIKE ‘a%o’ -> Finds any values that start with “a” and ends with “o”
example:
SELECT column_name(s) FROM table_name
WHERE column LIKE pattern;

Question 18

Q

IN

Answer

A

substitute for OR conditions
allows multiple values in where clause
example:
SELECT column_name(s) FROM table_name
WHERE column IN (value1, value2, …); -> values can also come from another select statement

Question 19

Q

BETWEEN

Answer

A

use for range condition between two values
example:
SELECT column_name(s) FROM table_name
WHERE column BETWEEN value1 AND value2;

Question 20

Q

Aliases

Answer

A

casting table or column as another name
used to make column/table names more readable
only work during execution of query

example:
SELECT column_name AS alias_name
FROM table_name AS alias_name;

    SELECT o.OrderID, o.OrderDate, c.CustomerName
    FROM Customers AS c, Orders AS o
    WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

Question 21

Q

JOINs

Answer

A

combine rows from 2+ tables
combination is through a common column between tables
INNER JOIN - returns matching values in both tables:
SELECT table1.column1, table2.column1, …
FROM table1
INNER JOIN table2 ON table1.column_name=table2.column_name;
LEFT JOIN - return all result from left table (table1) puls matching results from right table (table2) or NULL if right table has no match:
SELECT table1.column1, table2.column1, …
FROM table1
LEFT JOIN table2 ON table1.column_name=table2.column_name;
RIGHT JOIN - return all result from right table (table2) puls matching results from left table (table1) or NULL if left table has no match:
SELECT table1.column1, table2.column1, …
FROM table1
RIGHT JOIN table2 ON table1.column_name=table2.column_name;
*FULL JOIN - returns values when there is a match in either tables, in other words, return all records in both tables:
SELECT table1.column1, table2.column1, …
FROM table1
FULL JOIN table2 ON table1.column_name=table2.column_name;
*example:
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

Question 22

Q

UNION

Answer

A

UNION - combines 2 SELECT statements, basically combining two tables
they must have same schema: - similar datatypes, # of columns and column order
UNION only select DISTINCT values, UNION ALL takes duplicates
example:
SELECT column(s) FROM table1
UNION (ALL - for duplicates)
SELECT column(s) FROM table2;

Question 23

Q

GROUP BY

Answer

A

group rows with similar values into a summary
uses aggregate functions (COUNT MIN MAX SUM AVG) to group result-sets by column(s)

example:
SELECT AVG(column), column1
FROM table_name
GROUP BY column1;

    SELECT COUNT(CustomerID), Country
    FROM Customers
    GROUP BY Country
    ORDER BY COUNT(CustomerID) DESC;

Question 24

Q

HAVING

Answer

A

WHERE clause for GROUP BY
allows you to have condition/filter on the aggregate function after grouping
example:
SELECT AVG(column), column1
FROM table_name
GROUP BY column1
HAVING AVG(column) condition;

          SELECT COUNT(CustomerID), Country
          FROM Customers
          GROUP BY Country
          HAVING COUNT(CustomerID) > 5;

Question 25

Q

EXIST

Answer

A

condition to see if record(s) exist in another query
returns true if exists

example:
SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

    SELECT SupplierName
    FROM Suppliers
    WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);

Question 26

Q

ANY/ALL

Answer

A

where clause testing value against a subset
ANY returns true is 1 or more condition is met (any operates like IN operator)
ALL returns true is all conditions are met
example:
SELECT column_name(s) FROM table_name
WHERE column_name operator ANY/ALL -> operator: =, <>, !=, >, >=,

Question 27

Q

SELECT INTO

Answer

A

like select but save result into a new table
[IN external_bd] -> save table into different db
WHERE 1 = 0 -> creates new empty table
often use to create backups
example:
SELECT column1, column2, column3, …
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
```
    SELECT * 
    INTO CustomersBackup2020 IN 'Backup.mdb'
    FROM Customers;
```

Question 28

Q

INSERT INTO SELECT

Answer

A

copies a query result into an existing table
must have matching datatypes
existing records on target table are not affected (only adds new rows)
example:
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

Question 29

Q

CASE

Answer

A

sql version of if-else statement
returns result when condition is met
if no ELSE clause then returns NULL
format:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END AS alias;
example:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN ‘The quantity is greater than 30’
WHEN Quantity = 30 THEN ‘The quantity is 30’
ELSE ‘The quantity is under 30’
END AS QuantityText
FROM OrderDetails;

Question 30

Q

NULL functions

Answer

A

IFNULL() / ISNULL() returns specified value is expression is NULL, otherwise returns expression
COALESCE() returns 1st non-null value from expression to replace null 1s, if all null then returns specified value
format:
SELECT IFNULL(expression, value)
SELECT ISNULL(expression, value)
SELECT COALESCE(expression, value)

example:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

    SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
    FROM Products;

    SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
    FROM Products;

Question 31

Q

Stored Procedures

Answer

A

saved reusable sql statements
think of it as saving you sql statement into a variable or a function(you can pass parameters to it) that you can use later on
format:
CREATE PROCEDURE procedure_name @column_name datatype, …
AS
sql_statement (with condition(s) using given column_name(s))
GO;
execute/call/use a stored procedure:
EXEC procedure_name @column_name, …;
example:
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;
```
    EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';
```

Question 32

Q

Comments

Answer

A

line or block of code not to be executed
start comment with – for single comment line and /* for block of comments */
example:
–SELECT * FROM Customers;
SELECT * FROM Products;

          /*SELECT * FROM Customers;
          SELECT * FROM Products;
          SELECT * FROM Orders;
          SELECT * FROM Categories;*/
          SELECT * FROM Suppliers;

Question 33

Q

DATABASE

Answer

A

collection of tables
admin privilege is needed for database manipulation
can CREATE DROP BACKUP databases
use SHOW DATABASES for list of databases
CREATE use to create new database
format:
CREATE DATABASE databasename;
DROP use to delete an existing database
format:
DROP DATABASE databasename;
BACKUP use to create full backup of an existing database
format:
BACKUP DATABASE databasename
TO DISK = ‘filepath’
WITH DIFFERENTIAL; -> differential only backup whatever changes made since last backup
example:
BACKUP DATABASE testDB
TO DISK = ‘D:\backups\testDB.bak’
WITH DIFFERENTIAL;

          IF EXISTS(select * from sys.databases where name='yourDBname')
          DROP DATABASE yourDBname

      CREATE DATABASE yourDBname

Question 34

Q

TABLES

Answer

A

collection of data in the form of rows and columns
tables can be CREATE DROP or ALTER
to CREATE a basic table, need to specify table name and column(s) datatype
format:
CREATE TABLE table_name (
column1 datatype,
….
);

          CREATE TABLE new_table_name AS
              SELECT column1, column2,... FROM existing_table_name
              WHERE ....; -> create table from another table/statement
* example:
          CREATE TABLE Persons (
              PersonID int,
              Name varchar(255),
              Address varchar(255),
          );

      CREATE TABLE TestTable AS
      SELECT customername, contactname FROM customers; * to delete a table (DROP) format:
      DROP TABLE table_name; -> delete a table

      TRUNCATE TABLE table_name; -> delete DATA in a table, same as DELETE FROM table_name; * ALTER is used to modify existing table by add/delete/modify columns and/or add/drop constraints * format:
      ALTER TABLE table_name
      ADD column_name datatype;
      DROP COLUMN column_name;
      ALTER/MODIFY COLUMN column_name datatype;

Question 35

Q

Constraints/KEYs

Answer

A

use to specify rules in tables
can be specify when CREATE or ALTER tables:

format:
CREATE TABLE table_name (
column1 datatype,
…,
CONSTRAINT constraint_name CONSTRAINT_TYPE (column1, …)
);

    ALTER TABLE table_name
    ADD CONSTRAINT constraint_name CONSTRAINT_TYPE (column1, ...);

    ALTER TABLE table_name
    DROP CONSTRAINT constraint_name;

commonly used constraints:
> NOT NULL - Ensures that a column cannot have a NULL value. column must have not null value

example:
CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int
);

   ALTER TABLE Persons
   MODIFY/ALTER Age int NOT NULL;  > UNIQUE - Ensures that all values in a column are different, no duplicates in values

example:
CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
CONSTRAINT UC_Person UNIQUE (ID, Name)
);
> PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table, only 1 primary key per table
example:
CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID, Name)
);
> FOREIGN KEY - Uniquely identifies a row/record in another table, references a primary key in another table
example:
CREATE TABLE Orders (
OrderID int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
> CHECK - Ensures that all values in a column satisfies a specific condition, adding conditions to columns
example:
CREATE TABLE Persons (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=’Sandnes’)
);
> DEFAULT - Sets a default value for a column when no value is specified
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
> INDEX - Used to create and retrieve data from the database very quickly, adds indexes to columns in tables.
example:
CREATE INDEX index_name -> duplicates allowed (use CREATE UNIQUE INDEX index_name -> duplicates not allowed)
ON table_name (column1, column2, …);
```
   ALTER TABLE table_name
   DROP INDEX index_name;
```

Question 36

Q

Auto Increment

Answer

A

automatically generate unique numbers when inserting new record to table.
usually use auto increment column as primary key
automatically increase value by 1 for each record, use parameters to change starting and increment values
example:
CREATE TABLE Persons (
Personid int IDENTITY(10,5) PRIMARY KEY, – starts at 10 and increment by 5
Name varchar(255) NOT NULL,
Age int
);

Question 37

Q

DATES

Answer

A

difficult to work with date datatype because of format, especially if it has a time portion to it
difficult to filter date with time portion
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY – MySQL

Question 38

Q

Views

Answer

A

virtual table that is rendered at time of execution
it has up-to-date information since in fetches data at time of execution
create view format:
CREATE VIEW view_name AS – add this part to any select query to create a view
SELECT column1, column2, …
FROM table_name
WHERE condition;
use a view like using a table:
SELECT column(s)
FROM view_name
WHERE conditions;

Question 39

Q

Injection

Answer

A

user input

* very dangerous if not restricted

Question 40

Q

Hosting

Answer

A

allows your database to be access or hosted through the web via an ISP (internet service provider)

Question 41

Q

Casting

Answer

A

cast column datatype as another

Brainscape's Knowledge GenomeTM

Basics Flashcards

Brainscape's Knowledge Genome^TM