SQL

Question 1

How would you rate yourself in SQL?

Answer 1

1-4: Beginner
5-6: Intermediate
6+: Advanced

Answer 1: From the SDET point of view I would consider my SQL skills to be at an 8 out of 10.

Answer 2: I would rate myself 6 out 10 since I am not a database administrator but can perform the necessary queries as an SDET.

Question 2

What is a Database?

Answer 2

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS).

Question 3

What is RDBMS? How is it different from DBMS?

Answer 3

DBMS stands for Database Management System, and RDBMS is the acronym for the Relational Database Management system. In DBMS, the data is stored as a file, whereas in RDBMS, data is stored in the form of tables.

Question 4

What does SQL stand for?

Answer 4

SQL ⇒ Structured Query Language.

Question 5

What does Relational mean when we are referring to Relational Database?

Answer 5

Relational Databases are structured with tables connected through Primary and foreign key relationships.

Question 6

Are you familiar with any constraints in SQL?

Answer 6

Constraint ⇒ Rules for relational databases.

Primary Key ⇒ A unique identifier in a table that helps identify a specific row for a given table.

Uniquely identifies a record in the table.

Foreign Key ⇒ A foreign is a column within a table that is a primary key in another table.

Unique/composite Key ⇒ A column in a table that is not the primary key but only has unique values.

NOT NULL ⇒ There should always be a value defined.

Question 7

What tools are you using to work with your database?

Answer 7

Currently in my company the application database is a MySQL based server.

I use MySQL workbench to connect to my application database and run queries.

DB Viewer ⇒ MySQL workbench
DB server ⇒ MySQL

Question 8

What is a Primary Key?

Answer 8

Primary Key ⇒ A unique identifier in a table that helps identify a specific row for a given table.

Question 9

What is a Foreign Key?

Answer 9

Foreign Key ⇒ A foreign is a column within a table that is a primary key in another table.

Question 10

What is a UNIQUE key?

Answer 10

Unique/composite Key ⇒ A column in a table that is not the primary key but only has unique values.

Question 11

What is a Join? What are the different types?

Answer 11

What is a Join? ⇒ The process of combining data between 2 or more tables.

INNER
LEFT
RIGHT
FULL
SELF

Question 12

Have you heard of an index before?

Answer 12

Indexes are special items added to database tables to allow faster retrieval of data.

Question 13

What is a subquery?

Answer 13

A query within a query.
Example:
When you need another query to filter a specific value that becomes an input to your main query.

SELECT *
FROM table where column = (Inner query);

Need to find the employees that make more than the average salary?

Select employee_name, phone, email
From employees
Where avg salary > (SELECT avg salary from employees);

Question 14

What is UNION? What is the difference between UNION and UNION ALL?

Answer 14

UNION ⇒ When you combine the output between multiple independent select statements. (removes duplicate)

UNION ⇒ When you combine the output between multiple independent select statements. (removes duplicate)

UNION ALL => (output will be duplicates and non duplicates)

Rules:
1. Same number of columns in each query.
2. Order of the column and data type needs to match.