SQL Flashcards
What is a Primary key?
Primary Key (PK) is a column in the table, it’s uniquely identifies each record, and value is never duplicated within the same table. Primary Key cannot contain NULL values, a primary key is a special column in a database table that uniquely identifies each record. It must have a unique value for each row and cannot have null values.
What is a Foreign key?
Yes, Foreign key is usually found in another table. A key is used to connect or link two tables in a database. It can allow for the acceptance of Null Values. Yes, it’s possible to have more than one Foreign Key in a table, a foreign key is like a special field in one table that points to the primary key or a unique key in another table. It helps link information between tables.
How would you avoid duplicate records in a SELECT query?
Using the DISTINCT identifier eliminates duplicate records.
When and why do we use ORDER BY Clause?
in a SQL query it is used to sort the result-set in either ascending or descending order. By default, it sorts records in ascending order. If we want to sort them in descending order, we can use the DESC keyword after the ORDER BY.
What is the difference between BETWEEN and IN operators?
They are used for filtering based on multiple values, but they have distinct uses: The BETWEEN operator is used to select a range of data between two values. The IN operator allows us to specify multiple specific values to be included in the result set.
What is the difference between AND and OR operators?
The AND and OR operators in a WHERE clause they are used to filter records based on multiple conditions: The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
Let’s say there are some null values in my table. How would you test those values?
A field with a NULL value means it has no value. Comparing NULL values using traditional comparison operators like ‘=’, ‘<’, or ‘<>’ doesn’t work because NULL cannot be directly compared with other NULL values. To test for NULL values, you need to use the IS NULL operator.
When and why do we use GROUP BY Clause?
The GROUP BY clause in SQL is utilized to group the results of a SELECT query based on one or more columns. This statement is commonly paired with aggregate functions such as COUNT, MAX, MIN, SUM, and AVG to summarize data within each group.
Are you familiar with functions in SQL? What type?
Yes, I’m familiar with SQL functions. Such as LOWER, UPPER, and MAX, MIN, AVG. ( LOWER(): Changes a text to lowercase. UPPER(): Converts text to uppercase. MAX(): Gives the highest value in a set of data.MIN(): Provides the lowest value in a set. AVG(): Calculates the average value from a set of numbers)
What is the difference between the WHERE and HAVING clauses?
WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL. One syntax level difference is, WHERE is used before GROUP BY clause, while HAVING is used after GROUP BY clause.
What is a Subquery?
A subquery is a query inside another query, also called a nested query. It gets data used as a condition in the main query, helping to limit the information retrieved.
What is the difference between an inner and outer join?
An inner join shows rows when there is matching data in two or more compared tables. An outer join displays rows from both tables, including unmatched records from one or both tables.
How would you retrieve data that is located in 2 different tables?
Using the join of two tables based on condition we can retrieve data from two tables.
What is Self Join?
self JOIN is like regular join, but table is connected with itself.
How would you find the second highest salary from the table? Third highest?
To find the second highest salary from the table, SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); This query selects the maximum salary from the “employees” table where the salary is less than the maximum salary in the entire table, effectively giving us the second highest salary. For the third highest salary, SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)); This query builds on the logic of the second highest salary query, ensuring that the selected salary is less than both the maximum and the second maximum salaries in the table.
