SQL Flashcards
What is the difference between CHAR and VARCHAR datatype in SQL?
The difference between CHAR and VARCHAR has to do with the way data is stored. If you have a CHAR(20) and VARCHAR(20) and insert a string 5 characters long the VARCHAR will use enough storage to store 5 characters while the CHAR will also use storage to store 20 characters.
What Is Normalization?
Normalization is the process of table design to minimize the data redundancy.
There are different types of Noramalization forms in SQ
o First Normal Form (1NF): It removes all duplicate columns from the table. Creates table for related data and identifies unique column values
o First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines relationship between tables using primary key
o Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key
o Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF
What is BCNF?
BCNF is the Boyce Code Normal form. It is the higher version of 3Nf which does not have any multiple overlapping candidate keys
How to limit select certain number of rows?
- SQLServer – TOP
- MySQL/PostgreSQL – LIMIT
- Oracle – ROWNUM
How would you make an empty copy of an existing TABLE not using any Database specific functions or procedures?
You can use the CREATE TABLE AS Syntax
CREATE TABLE EMPLOYEE_COPY FROM SELECT * FROM EMPLOYEE WHERE 1=2;
How would you get all of the unique values stored in a column?
You can use the DISTINCT operator
SELECT DISTINCT JOB_ROLE FROM EMPLOYEES;
What is the difference between a Nested and Corelated subquery?
• Correlated Subquery: runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
o select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)
• Nested Subquery: runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
o select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
Can you have a subquery in the the FROM of another query? If so are there any requirements?
Yes you can, however you must explicitly give the result a name
What is the difference between the ‘%’ and ‘_’ operators?
- % means matches zero or more characters
* _ ( underscore ) means matching exactly one character
Write an SQL query to find names of employee start with ‘A’?
SELECT * FROM Employees WHERE EmpName like ‘A%’ ;
What Are The Different Types Of Sql Commands?
SQL commands are segregated into following types: • DDL – Data Definition Language • DML – Data Manipulation Language • DQL – Data Query Language • DCL – Data Control Language • TCL – Transaction Control Language
What Are The Different DDL Commands In Sql?
DDL commands are used to define or alter the structure of the database.
• CREATE: To create databases and database objects
• ALTER: To alter existing database objects
• DROP: To drop databases and databases objects
• TRUNCATE: To remove all records from a table but not its database structure
• RENAME: To rename database objects
What Are The Different DML Commands In Sql?
DML commands are used for managing data present in the database.
• SELECT: To select specific data from a database
• INSERT: To insert new records into a table
• UPDATE: To update existing records
• DELETE: To delete existing records from a table
What Are The Different DCL Commands In Sql?
DCL commands are used to create roles, grant permission and control access to the database objects.
• GRANT: To provide user access
• DENY: To deny permissions to users
• REVOKE: To remove user access
