SQL Concepts
What are the different types of joins?
(INNER) JOIN: Returns records that have matching values in both tables. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table. FULL (OUTER) JOIN: Returns all records where there is a match in either the left or the right table.
What is a sequence? Why would you create one?
A SEQUENCE is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in ascending or descending order at a defined interval and can be configured to restart when exhausted. This is typically used for PRIMARY KEY generation.
What is a trigger? Why would you use a trigger (give an example)?
A trigger is a set of actions that are run automatically when a specified change operation (INSERT, UPDATE, or DELETE) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data and keeping an audit trail. It can also be used on a sequence to auto-increment every time a record is inserted into the database.
What is ACID?
ACID is an acronym that helps to remember the fundamental principles of a transnational system. Atomic: Transaction acting on several pieces of information complete only if all pieces successfully save. All or nothing. Consistent: The saved data cannot violate the integrity of the database. Interrupted modifications are rolled back to ensure the database is in a state before the change takes place. Isolation: No other transactions take place and affect the transaction in question. This prevents "mid-air collisions" Durability: System failures or restarts do not affect committed transactions.
How would you update the customers table and add a credit column which can store numbers up to 999.99.
ALTER TABLE customer ADD credit DECIMAL(5,2) CHECK(credit <= 999.99);
What is the difference between using a join and using a subquery?
Subqueries can be used to return either a singular value or a row set while joins are used to return entire rows.
What is TCL? What operations does it entail?
TCL stands for Transaction Control Language and it is used to handle transactions within the database. Some of its operations are: COMMIT: Commits a transaction ROLLBACK: Rollbacks a transaction in case of any error occurs SAVEPOINT: Sets a savepoint within a transaction SET TRANSACTION: Specify characteristics for the transaction
What is the difference between values stored for a date verses a timestamp?
TIMESTAMP datatype is used for values that contain both date and time. TIMESTAMP ranges from '1970-01-01 00:00:01'. TIMESTAMP converts the values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
What datatype would you use to store several gigabytes of characters?
The LONGTEXT datatype can be used to store up to 4GB of characters.
After updating the customers table, how would you give all records a ten dollar credit?
UPDATE customer SET credit = 10.00;
How would you remove all records from a table but keep the table definition without using delete to delete all the records in that table?
Use TRUNCATE. TRUNCATE TABLE customer;
If you are going to store a variable length string using characters from a foreign language, what datatype would you use?
Use a Unicode datatype such as nchar, nvarchar or nvarchar(max). Unicode data types take two bytes per character.
You have a column in your table called creationDate which is a timestamp. How would you set that timestamp to the current system date of the database?
Use the CURRENT_TIMESTAMP function NSERT INTO customer VALUES (13, 'Conner', 'Kirschman', '[email protected]', '10.00', CURRENT_TIMESTAMP);
How would you remove a table from the database? Can you undo this?
Using DROP DROP TABLE customer; This can only be undone with a rollback if it is done within a transaction.
If you create or update a constraint in a table, can you use rollback to remove that constraint?
Yes
Can a PRIMARY KEY reference multiple columns?
Yes, a PRIMARY KEY can consist of a single column or multiple.
If you are giving an image, what datatype would you use?
You would store an image the same way you would store any other BLOB, that being VARBINARY(max).
What is the difference between char(10) and varchar2(10)?
CHAR(n) will always be n bytes long, it will be blank padded on insertion to ensure this. VARCHAR2(n) on the other hand will be 1 to n bytes long and will not be padded.
How would you create an index on a table (give an example)?
CREATE INDEX index_name ON table_name (column1, column2, ...);
What is CRUD?
CRUD stands for CREATE, READ, UPDATE, DELETE and are the four basic functions of persistent storage.
You create a new table defined as follows: CREATE TABLE customer ( person_id int(38) PRIMARY KEY, first_name varchar(255) NOT NULL, last_name varchar(255) NOT NULL, email varchar(100) NOT NULL); And then run the SQL statement: insert into customers values (10, 'Bob', 'Smith', '[email protected]'); Immediately after running that statement, you run the following SQL statement: select count(*) count from customers where last_name = 'Smith'; What does the SQL return and why?
It returns 1 because there is 1 record with the last_name 'Smith'
Can a PRIMARY KEY have a null value?
No a PRIMARY KEY cannot be null. It must be a unique value in the database.
What is 2NF? How do you know if a table is in 2NF? Give an example.
Rules for 2NF: Be in 1NF Have a single column PRIMARY KEY
What is 3NF? How do you know if a table is in 3NF? Give an example.
Rules for 3NF: Be in 2NF Have no transitive functional dependencies (a transitive functional dependency is when changing a non-key column might cause any of the other non-key columns to change).
Using the table in 25, how would you get all email addresses that end in 'att.com'?
SELECT * from customer WHERE email LIKE '%att.com';
Using the table in 25, how would you get all records whose last name is 'Smith'?
SELECT * from customer WHERE last_name = 'Smith';
What is SQL injection?
SQL Injection is the placement of malicious code in SQL statements , usually during web page user-input.
What are constraints? Give some examples other than PRIMARY KEY and not null.
SQL constraints are used to specify rules for the data in a table. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. Some of these constraints include: NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK DEFAULT INDEX
What is SQL?
SQL stands for Structured Query Language and it is used to communicate with a database. It is the standard language for relational database management systems (RDBMSs). SQL statements are used to perform tasks such as update or retrieve data from a database. The standard commands are 'Select', 'Insert', 'Update', 'Delete', 'Create', and 'Drop'.
What is isomorphic?
Isomorphism is a structure-preserving mapping between two structures of the same type that can be reversed by an inverse mapping.
What is 1NF? How do you know if a table is in 1NF?
1NF refers to Normalization which is a concept in database design that reduces redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. Rules for 1NF: Each table cell should contain a single value (Not null) Each record needs to be unique
What is DBMS?
A DBMS is a Database Management System which refers to a technology solution that is used to optimize and manage the storage and retrieval from databases. DBMS offers a systematic approach to manage databases via an interface for users as well as workloads accessing the databases via apps.
Does a FOREIGN KEY have to reference a PRIMARY KEY?
A FOREIGN KEY must refer to an entire PRIMARY KEY, and not just a part of it.
What is the difference between a PRIMARY KEY and a FOREIGN KEY?
A PRIMARY KEY constraint is a column that uniquely identifies every row in the table of the relational database management system, while FOREIGN KEY is a column that creates a relationship between two tables. PRIMARY KEY never accepts null values whereas FOREIGN KEY may accept multiple null values.
What is a PRIMARY KEY? Why would you use one?
A PRIMARY KEY is a constraint which uniquely identifies each record in a table. A PRIMARY KEY must contain unique values and cannot contain NULL values. A table can have only one PRIMARY KEY and in the table, this PRIMARY KEY can consist of a single or multiple columns.
What is DCL? What operations does it entail?
DCL stands for Data Control Language and it is used to handle rights, permissions, and other controls in a database system. Some of its operations are: GRANT: Gives users access privileges to database REVOKE: Withdraw users access privileges given by using the GRANT command
What is DDL? What operations does it entail?
DDL stands for Data Definition Language and it is used for creating and modifying database objects such as tables, indices, and users. Some of its operations are: CREATE: Used to create the database or its objects (table, index, function, view, store procedure, and triggers) DROP: Used to delete objects from the database ALTER: Used to alter the structure of the database TRUNCATE: Used to remove all the records from a table including all spaces allocated for the records removed COMMENT: Used to add comments to the data dictionary RENAME: Used to rename an object existing in the database
Using the table in 25, how would you remove all records where person_id is greater than 10?
DELETE FROM customer WHERE person_id > 10;
What is DML? What operations does it entail?
DML stands for Data Manipulation Language and it is used for adding, deleting, and modifying data in a database. Some of its operations are: INSERT: Used to insert data into the table UPDATE: Used to update existing data within a table DELETE: Used to delete records from a database table
What is DQL (or DRL)? What operations does it entail?
DQL stands for Data Query Language and it is used for performing queries on the data within schema objects. It is to get some schema relation based on the query passed to it. Some of its operations are: SELECT: Used to retrieve data from the database
