Basics of SQL Flashcards

1
Q

What does SQL stand for?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is RDBMS?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a PRIMARY KEY and FOREIGN KEY?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Is SQL case sensitive?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How to write a string inside SQL?

A

Use ‘string’ SINGLE QUOTES.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How would you destroy a database called Flights in SQL?

A

DROP DATABASE Flights;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How would you create a database called SimpsonsDB?

A

CREATE DATABASE SimpsonsDB;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

USE SevenHells;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How would you make a table called prime?

A

CREATE TABLE prime;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

CREATE TABLE Animals (
Dogs INT
);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

ALTER TABLE Animals
ADD CatNames VARCHAR(100);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Does:
ALTER
TABLE
tableName;

Run the same as:
ALTER TABLE tableName;

A

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 ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

ALTER TABLE Painting
ADD columnName;

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

17
Q

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

A

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 ()

18
Q

What is the ADD keyword used for?

A

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

ADD Columns

19
Q

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

A

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

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

20
Q

What does the SELECT keyword do?

A

SELECT - retrieves data from a table in a DB.

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

21
Q

What is * in SQL?

A
  • = wildcard/any/all columns.
22
Q

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

A

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)

22
Q

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

A

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’

23
Q

What does the AS keyword do?

A

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

24
Q

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

A

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

25
Q

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

A

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.

26
Q

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

A

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

27
Q

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

A

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.

28
Q

In the following statement, which words would be included in the filtered data?
WHERE x LIKE ‘%par%’

“leopard flying”, “parrot”, “ampersand”

A

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

29
Q

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

A

DELETE FROM mablesTable
WHERE id = 5;

30
Q

What is the purpose of JOIN in SQL?

A

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.

31
Q

What does LEFT JOIN do?

A

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.

32
Q

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

A

– Double Dash comments out in SQL

33
Q

What does the DISTINCT keyword do?

A

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

34
Q

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

A

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

35
Q

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

A

AND > OR

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