Basics of SQL

Question 1

What does SQL stand for?

Answer 1

Structured Query Language (SQL) - a language for managing data in a RDMS - Relational Database Management System (where related data is grouped together in tables).

Question 2

What is RDBMS?

Answer 2

RDMS - Relational Database Management System = where related data is grouped together in tables which in turn can be linked by relationships.

Question 3

What is a PRIMARY KEY and FOREIGN KEY?

Answer 3

Primary key = usually a table column header/field/unique identifier for records stored within the table.

Foreign key is a field (table column header) or collection of fields that refer to the headers/primary key of another table.

Question 4

Is SQL case sensitive?

Answer 4

No, SQLanguage is not case-sensitive however it is a BEST PRACTICE to always use capitals for KEYWORDS.

Question 5

How to write a string inside SQL?

Answer 5

Use ‘string’ SINGLE QUOTES.

Question 6

How would you destroy a database called Flights in SQL?

Answer 6

DROP DATABASE Flights;

Question 7

How would you create a database called SimpsonsDB?

Answer 7

CREATE DATABASE SimpsonsDB;

Question 8

How to start working in a database called SevenHells?/Change the active DB to it.

Answer 8

USE SevenHells;

Question 9

How would you make a table called prime?

Answer 9

CREATE TABLE prime;

Question 10

Create a SQL table called Animals with a column called Dogs with a INT type of data.

Answer 10

CREATE TABLE Animals (
Dogs INT
);

Question 11

Add another column/field to the Animals table called CatNames that takes in string values with a maximum string length of 100 chars.

Answer 11

ALTER TABLE Animals
ADD CatNames VARCHAR(100);

Question 12

Does:
ALTER
TABLE
tableName;

Run the same as:
ALTER TABLE tableName;

Answer 12

Yes! These statements run identically even though they are split over multiples lines as the CLI interpreter reads everything as continuous blocks until there is a ;

Question 13

Why should every SQL table you make have an ID column/field?

Answer 13

So that if there is the same data on different rows (e.g. two users enter name: Mike, age: 20) then you can tell them apart with the Unique IDentifier column value.

Question 14

How would you create a table called Painting with a foreign key with a field of: colour linking to a table called anomalies with a field/column of extra?

Answer 14

CREATE TABLE Painting (
colour VARCHAR(20),
FOREIGN KEY (colour)
REFERENCES anomalies(extra)
);

Question 15

How to add a column called columnName intoto a table called Painting. Add in the values to the column: purple, yellow, red

Answer 15

ALTER TABLE Painting
ADD columnName;

INSERT INTO Painting (columnName)
VALUES (‘purple’), (‘yellow’), (red’);

Question 16

How to add a value of “X” into a table called Letters in an already added field/column headed LastLetters?

Answer 16

INSERT INTO Letters (LastLetters)
VALUES (‘X’);

Question 17

What is the INSERT INTO keyword used for? What keyword is it most commonly used with?

Answer 17

To INSERT INTO specific data/rows into a table in combination with the VALUES keyword.

E.g. INSERT INTO TableName (Col/FieldName)
VALUES (1), (2,), (3)

INSERT data INTO the DB. each value/bit of data/ROW needs to be in ()

Question 18

What is the ADD keyword used for?

Answer 18

To modify the structure of an existing table (e.g. ADD columnName).

ADD Columns

Question 19

What two steps to add the names of Steve and Frank to a table called people in a new column called Men?

Answer 19

Step 1:
ALTER people
ADD Men VARCHAR (20);

Step 2:
INSERT INTO people (Men)
VALUES (Steve), (Frank);

Question 20

What does the SELECT keyword do?

Answer 20

SELECT - retrieves data from a table in a DB.

E.g. SELECT * FROM MyFirstTable; - selects all columns from the MyFirstTable table.

Question 21

What is * in SQL?

Answer 21

• = wildcard/any/all columns.

Question 22

How to SELECT a maximum of 50 numbers from a column called/headed Nums inside a table called Math?

Answer 22

SELECT Nums FROM Math LIMIT 50

LIMIT = limits the SELECT query to retrieve only the first X results that meet the queries condition (in this case being in the column Nums). The ORDER BY clause can be added below to sort the condition by columnName ASC (ASCending) or DESC (DESCending)

Question 22

What KEYWORD is generally used to create a unique value ID column in SQL?

Answer 22

AUTO_INCREMENT keyword is often used to create a unique (auto-incrementing) id INT column PRIMARY KEY:

CREATE TABLE MyTable (
id INT AUTO_INCREMENT PRIMARY KEY); – The ‘id’

Question 23

What does the AS keyword do?

Answer 23

Assigns an AliaS to a column or table for the duration of a query.

Question 24

What is the AUTO-INCREMENT keyword most common use case?

Answer 24

AUTO-INCREMENT = used to auto generate a unique value for a column - generally used for PRIMARY KEYS like the ID column = n=n+1

Question 25

What keyword would you use to change a value/data in an SQL table?

Answer 25

UPDATE is used to change or modify the values in one or more rows of a table in an SQL database. e.g. UPDATE table1 SET column2 = "Steve" WHERE id=2; (this would change the second row of data column2 field to "Steve". The updated value must be of the same data type (INT, VARCHAR, etc) as the initial value. To change the value type of a pre-existing table you'd need to: ALTER TABLE table1 MODIFY column2 INT; etc.

Question 26

What does the WHERE keyword do in the statement: SELECT * FROM x-men WHERE ages = >35 ;

Answer 26

The WHERE keyword is a filter. Here it filters all x-men that have an ages column value >35.

Question 27

In the following statement, what do the %'s represent: WHERE x LIKE '%c%' ;

Answer 27

The % operator is used with the LIKE keyword to find similar string values (search for a pattern). It represents any amount of characters (inc. 0) which can be any character. %=anything E.g. church, bloody excellent, cry would all be selected by this WHERE filter. Any string values that contain 'c' in this example.

Question 28

In the following statement, which words would be included in the filtered data? WHERE x LIKE '%par%' "leopard flying", "parrot", "ampersand"

Answer 28

leoPARd flying and PARrot would be included as they contain the 'par'

Question 29

How would you delete the 5th value in a table called mablesTable that has an id column?

Answer 29

DELETE FROM mablesTable WHERE id = 5;

Question 30

What is the purpose of JOIN in SQL?

Answer 30

The JOIN statement is used to COMPARE and COMBINE rows from multiple tables based on a related column they both have. There are several different variations of the JOIN statement for different purposes: JOIN (shorthand for INNER JOIN) - returns only rows that have matching values in both tables LEFT JOIN - returns ALL rows from the left table (on the left side of the statement) and the matched value rows from the right. RIGHT JOIN - returns ALL rows from the right table combined with the MATCHING ONLY rows from the left table. (makes no sense to use right join really, just flip the tables around and use LEFT JOIN.) FULL JOIN - returns all rows when there is a match in either left or right tables. If there is no match NULL values are returned for the side with the missing value.

Question 31

What does LEFT JOIN do?

Answer 31

LEFT JOIN - returns ALL rows from the left table (written on the left side of the statement) matching or not and only the MATCHING value rows from the right side table. LEFT JOIN = ALL left side rows combined with right side value rows that MATCH the left side row they are being compared with.

Question 32

What syntax do you use to comment out a line in SQL?

Answer 32

-- Double Dash comments out in SQL

Question 33

What does the DISTINCT keyword do?

Answer 33

DISTINCT only shows unique/DIFFERENT values/results from a column/set of columns.

Question 34

How is the date of 15th June 1932 written in SQL?

Answer 34

1932-06-15 DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: YYYY-MM-DD HH:MI:SS YEAR - format YYYY or YY

Question 35

What is the order of precedence for AND and OR operators? How would you override this order?

Answer 35

AND > OR Can use () to manually override order of precedence in the same was as you can with *+-/.