Advanced Syntax

Question 1

What time data can a TIME format hold?

Answer 1

TIME can only hold time information

Question 2

What data can DATE hold?

Answer 2

DATE can only hold date information, no time

Question 3

What information can the TIMESTAMP datatype hold?

Answer 3

TIMESTAMP can hold both date and time information

Question 4

What information can the TIMESTAMPTZ datatype hold?

Answer 4

TIMESTAMPTZ can contain date, time and timezone information

Question 5

What does the EXTRACT() function do?

Answer 5

• Allows you to extract or obtain a sub-component of a date value
• EXTRACT(YEAR FROM data_col)

Question 6

What does the AGE() function do?

Answer 6

• Calculates and returns the current age given a timestamp
• Useage:
  
  • AGE(date_col)
• Returns
  
  • 13 years 1 mon 5 days 01:34:13.003423

Question 7

What does the TO_CHAR() function do?

Answer 7

Lets you parse the datetime to a specific format. For example:

SELECT TO_CHAR(payment_date, ‘MM-YYYY’)

Documentation: https://www.postgresql.org/docs/12/functions-formatting.html

Question 8

Can you use EXTRACT, AGE, TO_CHAR functions outside of SELECT?

Answer 8

Yes, they can be used to filter out results inside a WHERE clause for example

Question 9

How can I concatenate strings inside a query?

Answer 9

SELECT first_name || ‘ ’ || last_name as full_name
FROM customer

Result:

Trevor Lahey

Question 10

How can I uppercase strings inside a query?

Answer 10

SELECT upper(first_name)

Question 11

What is a sub query?

Answer 11

• A sub query allows you to construct complex queries, essentially performing a query on the results of another query
• The syntax is straightforward and involves two SELECT statements

Example:

SELECT * FROM table
WHERE something > (SELECT somethingElse FROM table)

Question 12

Given the following table named test_scores:

student,grade

How can I find all students that are better than average using a sub query?

Answer 12

SELECT student,grade
FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)

Question 13

What gets executed first? The main query or the sub query?

Answer 13

The sub query

Question 14

Can you add other tables using sub queries?

Answer 14

Yes, just like JOIN, sub queries can access data from other tables.

Example:

SELECT student,grade
FROM test_scores
WHERE student IN (SELECT student FROM honor_roll_table)

Question 15

What operator do I need to use if the sub query results in multiple values?

Answer 15

IN operator

Question 16

Can a sub query substitute a JOIN?

Answer 16

Yes, JOIN commands such as INNER JOIN, LEFT OUTER JOIN etc will generally execute faster, however some people prefer sub queries because they are easier to read. Both are valid in most use cases

Postgresql will actually rewrite a query to a JOIN or sub query when it thinks it could lead to faster performance.

Question 17

Can sub queries and JOIN statements be used in conjunction with each other?

Answer 17

Yes, example:

SELECT film.film_id,title
FROM film
WHERE film_id IN
(SELECT inventory.film_id
FROM rental
JOIN inventory ON inventory.inventory_id = rental.inventory_id
WHERE return_date BETWEEN ‘2005-05-29’ AND ‘2005-05-30’)
ORDER BY title

Question 18

What does the EXISTS operator do?

Answer 18

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

Example:

SELECT first_name, last_name
FROM customer as c
WHERE EXISTS(
SELECT * FROM payment as p
WHERE p.customer_id = c.customer_id
AND amount > 11
)

Question 19

What would the following query look like if it was written using INNER JOIN:

SELECT first_name, last_name
FROM customer as c
WHERE EXISTS(
SELECT * FROM payment as p
WHERE p.customer_id = c.customer_id
AND amount > 11
)

Answer 19

SELECT first_name,last_name
FROM customer
JOIN payment p on customer.customer_id = p.customer_id
WHERE p.customer_id = customer.customer_id AND amount > 11

Question 20

What would the following query look like if it was written using INNER JOIN:

SELECT first_name, last_name
FROM customer as c
WHERE EXISTS(
SELECT * FROM payment as p
WHERE p.customer_id = c.customer_id
AND amount > 11
)

Answer 20

SELECT first_name,last_name
FROM customer
JOIN payment on customer.customer_id = p.customer_id
WHERE amount > 11
ORDER BY first_name

Question 21

Advantages and Disadvantages of JOIN

Answer 21

Advantages Of Joins:

• The advantage of a join includes that it executes faster.
• The retrieval time of the query using joins almost always will be faster than that of a subquery.
• By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query. This means you can make better use of the database’s abilities to search through, filter, sort, etc.

Disadvantages Of Joins:

• Disadvantage of using joins includes that they are not as easy to read as subqueries.
• More joins in a query means the database server has to do more work, which means that it is more time consuming process to retrieve data
• As there are different types of joins, it can be confusing as to which join is the appropriate type of join to use to yield the correct desired result set.
• Joins cannot be avoided when retrieving data from a normalized database, but it is important that joins are performed correctly, as incorrect joins can result in serious performance degradation and inaccurate query results.

Question 22

Advantages and Disadvantages of Sub queries

Answer 22

Advantages Of Subquery:

• Subqueries divide the complex query into isolated parts so that a complex query can be broken down into a series of logical steps.
• It is easy to understand and code maintenance is also at ease.
• Subqueries allow you to use the results of another query in the outer query.
• In some cases, subqueries can replace complex joins and unions.

Disadvantages of Subquery:

• The optimizer is more mature for MYSQL for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as join.
• We cannot modify a table and select from the same table within a subquery in the same SQL statement.

Question 23

What is a self join?

Answer 23

The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.

This is useful if I want to format the results of a table clearer.
For example given a table with employees and each employee also has a manager_id but each manager is also an employee with a employee_id, in which case I can show the managers name next to the employees name like so:

SELECT

employee. Id,
employee. FullName,
employee. ManagerId,
manager. FullName as ManagerName

FROM Employees employee

JOIN Employees manager

ON employee.ManagerId = manager.Id