SQL

Question 1

What is a Databse

Answer 1

An organized collection of data stored on a computer.

Question 2

What does DBMS stand for

Answer 2

Database Management System

Question 3

What does RDBMS stand for?

Answer 3

Relational Database Management System

Question 4

What is SQL

Answer 4

SQL stands for Structured Query Language

It is the standard language used for accessing, and storing data from a database

Question 5

Name some popular Relational Database Management Systems.

Answer 5

• MySQL
• Oracle
• PostgreSQL
• IBM DB2
• MariaDB

Question 6

What are Tables, Fields, and Records in an SQL Database?

Answer 6

A Table is an organized collection of data defined by the data’s model, that is organized in rows in columns
A field, or a table column, is a label that defines which data should be stored there for each record in the table
A Record, or table row, is a single set of data comprised of all the required fields of the table

Question 7

List and describe all of the SQL Constraints

Answer 7

NOT NULL: Any value inserted in to this field cannot be NULL
CHECK: Verifies that the values of the affected field satisfy the stated condition
DEFAULT: Assigns a value to a field if no other value was assigned to it.
UNIQUE: For the field marked Unique, every record in the table must have a unique value.
INDEX: indexes a field for faster data retrieval
PRIMARY KEY: A unique field that is used to identify each individual record
FOREIGN KEY: A field that allows the database to link records across different tables.

Question 8

How does a foreign key work?

Answer 8

When two tables contain related data, a foreign key is used to establish a link between the two tables.

When a relationship is created, the child table will receive a foreign key field and each record that has a relationship to a record on the parent table will have a foreign key that refers to the Primary Key Value of the parent record.

Question 9

Describe the AS clause

Answer 9

Allows us to define a new name for a field when creating a join.

Question 10

Describe an Inner Join

Answer 10

Compares all records from two tables and determines if any values of a specified column in table A match any values of a specified column in table B

Returns only the full records from both tables where the specified fields matched

Question 11

Describe a Full Outer Join

Answer 11

Returns all data from both tables and matches the data records that have matching data in the specified field

Question 12

Describe an Exclusive Outer Join

Answer 12

Opposite of an inner join

Will only return records where the data in the selected field does not have a match.

Question 13

Describe an Asymmetrical Join or

Answer 13

Returns all records from table A and only records from table B where the data in the specified fields match

Question 14

Describe an Exclusive Asymmetrical Join

Answer 14

Selects only the records from Table A that do not have matching data in the specified field in table B

Question 15

What is the difference between left and right joins?

Answer 15

The only difference in SQL is the order that the tables are referenced.

In a Left Join, table A is referenced first and table B is joined on to table A

In a Right Join, Table B is referenced first and table A is joined on to table B

Question 16

What is a Union statement

Answer 16

Used to directly combine data from two or more tables without any explicit data relationship

Question 17

What is a Sub-query

Answer 17

A sub query is a query that selects a set of records from a previous query or a query that uses a query that returns a single value (like a mathematical function query) in order to create a second query.

Question 18

What is a Self-Join

Answer 18

A self join only uses one table.

It is used to match the data of two related columns within the singular table.

Question 19

What is a cross join

Answer 19

A cross join between two tables will return a query that includes all possible combinations of each record in each table.