SQL
• Design a data model for a school
• Q1) Find the number of unique days each employee worked Emp Id
Task Id Start date End date 1 1 Monday Wednesday 1 2 Monday
Tuesday 1 3 Friday Friday 2 1 Monday Friday 2 1 Tuesday Wednesday
Hint: Calendar day table or date dimension table Calendar_day
Calendar_day_of_week Calendar_year Calendar_month 1900/01/01
Wednesday (3) 1990 1
• How many customers placed orders every month?
• Table 1: Customer Date customer_id order_id units country
2019/07/01 A 112 5 US 2019/07/02 A 211 4 US 2019/08/02 B 511 4
EU 2019/09/01 C 322 1 JP 2019/09/01 C 322 2 JP 2019/08/05 A 378 6
US 2019/09/10 A 456 7 US
What is index ?
• Indexes are used to retrieve data from the database more quickly
than otherwise.
• The users cannot see the indexes, they are just used to speed up
searches/queries.
• Can be non-unique.
Partition
• We create function to partition data
• It creates virtual tables.
What is a foreign key ?
Give example of joins
What is a SQL view ?
• Virtual table.
• Contents obtained from base tables using queries.
Write a query to get the third
highest salary
Union & Union vs union all
• We use union to combine datasets.
• Order & number of the columns should be the same in both queries.
• Union selects only distinct values, it will not include duplicates.
• Union all selects all values. Duplicate rows are not eliminated.
What is a self join & when would you
use it ?
• Self-join is useful when you have to evaluate the data of the table
with itself.
• A common use case is when the table stores entities (records) which
have a hierarchical relationship between them.
• For
How to find duplicates in table ?
• We can group by using a column & than we can use having clause to
show the results where groups count is greater than 1.
• Another way is to appky
What is a stored procedure in SQL ?
• Prepared sql code that we can save & execute & resuse.
• Can be passed parameters.
What is the diff bw truncate &
delete ?
• The TRUNCATE command is used to delete all the rows from the table
and free the space containing the table.
•
The DELETE command deletes only the rows from the table based on
the condition given in the where clause or deletes all the rows from
the table if no condition is specified. But it does not free the space
containing the table.
Aggregate vs Scalar
• An aggregate function performs operations on a collection of values
to return a single scalar value. Usually used alongwith group by &
having. For ex avg, count, max, min
• Scalar function returns a single valuye based on input. Len(), Round()
What is a user defined function ?
• The user-defined functions in SQL are like functions in any other
programming language that accept parameters, perform complex
calculations, and return a value. They are written to use the logic
repetitively whenever required.
• UDFs are a way to extend MYSQL with a new function that works like
native in built functions.
• They are of scalar function type
• Table valued functions.
Procedures vs user defined
functions
• A function always return value & cannot perform data manipulation
statements i.e. insert delete & update.