SQL Flashcards
What is the difference between DELETE and TRUNCATE?
DELETE is a DML command and TRUNCATE is a DDL command
The best image of the difference is that:
if you do DELETE TABLE, this would remove all the rows of data from a table while keeping the tables structure.
TRUNCATE TABLE would completely get rid of the table.
Difference between DML and DDL?
DML stands for data manipulation language. It is used to manipulate the data itself.
Insert, Update, Select, and Delete statements are all a part of DML.
DDL stands for data definition language which is used to define data structures.
create table, alter table, drop, rename and alter are all DDL statements
Difference between where and having?
both commands filter the table to meet a certain condition.
Having is used with the Group By statement to filter rows after collecting Whereas where is used without the Group By.
What is an index in SQL? When would you use an index?
indexes are lookup tables used by the database to perform data retrieval more efficiently.
This will speed up SELECT and WHERE clauses but slow down UPDATE and INSERT.
What are aggregate functions in SQL?
An aggregate function performs a calculation on a set of values and returns a single value.
Three common ones are COUNT, SUM and AVG.
AVG ignores Null values
What SQL commands are utilized in ETL?
SELECT, JOIN, WHERE, ORDER BY, GROUP BY
Does JOIN order affect SQL query performance?
Yes, if you have 3 tables. two large and one small. You join the two large together and then the small. The query is going over more rows of data so it is using more processing.
How do you change a column name by writing a query in SQL?
ALTER TABLE TableName
RENAME COLUMN OldColumnName TO NewColumnName;
How do you handle duplicate data in SQL?
Okay so to clarify for this question:
what kind of data is being processed?
what type of values can users duplicate?
Distinct key on primary_key of the table which is usually an id of some sort.
You can also use the Group By function on two values
Most complicated SQL query you wrote recently
Sure just before this call I wrote one that was pretty complicated.
I had to find the top 3 customers we had by revenue for each year beggening in 2019 for a report.
The reason this was
How to add Partitions?
ALTER TABLE sales
ADD PARTITION jan99 VALUES LESS THAN ( ‘01-FEB-1999’ )
TABLESPACE tsx;
How to select a partition?
mysql> SELECT * FROM employees PARTITION (p1);
+—-+——-+——–+———-+—————+
+—-+——-+——–+———-+—————+
| 5 | Mary | Jones | 1 | 1 |
| 6 | Linda | Black | 2 | 3 |
| 7 | Ed | Jones | 2 | 1 |
| 8 | June | Wilson | 3 | 1 |
| 9 | Andy | Smith | 1 | 3 |
+—-+——-+——–+———-+—————+
5 rows in set (0.00 sec)
id | fname | lname | store_id | department_id |
How to update a row in SQL?
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;
How to insert values into SQL?
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);
how to insert a new column conditionally in SQL?
ALTER TABLE person
ADD salary int(20);
UPDATE persons SET salary = ‘145000’ where Emp_Id=12;
