SQL Question Bank

What is a cascade delete?

"A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server. A foreign key with cascade delete can be created using either a CREATE TABLE statement or an ALTER TABLE statement."

What is the difference between a global table and a local table in SQL?

"A local temp table is visible to the creator that is connected only, disbands after disconnect. A global temp table is saved in the database and visible by all users, even after the initial creating user disconnects."

What is an entity relational diagram (ERD)?

"An entity-relationship diagram—otherwise known as an ERD—is a data modeling technique that creates an illustration of an information system's entities and the relationships between those entities. There are 3 ingredients in a standard entity-relationship diagram: •Entities, which represent people, places, items, events, or concepts. •Attributes, which represent properties or descriptive qualities of an entity. These are also known as data elements. •Relationships, which represent the link between different entities. Entities, attributes, and relationships can be represented in one of three ways: with a conceptual model, logical model, or physical model. These models increase in complexity as you move from conceptual to logical to physical. It's usually best to start with a conceptual ERD model, so you can understand—at the highest level—the entities in your data and how they relate to each other. As you transform a conceptual ERD to a physical model, you'll learn exactly how to implement modeled information into the database of your choice. There are two main types of ERD notations: Crow's Foot notation and Chen notation."

How do you change the column order of an existing table in SQL that has data in it?

"Assuming columns: a, b, c, d (As they appears in a 'select *'), You would have to indicate specifically which columns you want, in the order you want: SELECT d,a,b,c FROM TABLE; (You could also recreate the table)"

View Syntax:

"CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition"

What is an explicit cursor?

"Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. CURSOR cursor_name IS select_statement; Working with an explicit cursor involves four steps: Declaring the cursor for initializing in the memory Opening the cursor for allocating memory Fetching the cursor for retrieving data Closing the cursor to release allocated memory"

What is the difference between LEFT JOIN, RIGHT JOIN, and FULL JOIN?

"INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables"

How would you go about deleting duplicate rows in a table in SQL?

"Identify duplicates via a query thqat utilizes a count(*) aggragate, where the count is greater than 1. Set rowcount to amount of total specific duplicates, then delete the records individually."

Difference between JDBC and ODBC

"JDBC provides a connection between front end java applications to backend databases. ODBC provides the same services, except for other applications other than java."

What is a cross join? Difference between cross join and cartesan ?

"Joins two tables via a cartesian product. (All possible combinations for each table's entities). Cross join and certesian are the same."

What are various constraints in SQL?

"NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value for a column"

What is normalization?

"Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. There are six normal forms, though many organizations only use the first three. (1NF) Eliminate repeating groups in individual tables. Create a separate table for each set of related data. Identify each set of related data with a primary key. (2NF) A table is in 2NF if it is in 1NF and every non-key attribute of the table is dependent on the primary key as a whole. Therefore, a table is automatically in 2NF if the table does not use a composite key. (3NF) The table is in third normal form if it is in 2NF and every non-key attribute is not dependent upon any non-key attribute (aka transitive dependency)"

What are one-to-one, one-to-many and many-to-many relationships, and how do you implement?

"One-to-One (1:1) relationship is defined as the relationship between two tables where both the tables should be associated with each other based on only one matching row. This relationship can be created using Primary key-Unique foreign key constraints. With One-to-One Relationship in SQL, for example, a person can have only one passport. The One-to-Many (1:M) relationship is defined as a relationship between two tables where a row from one table can have multiple matching rows in another table. This relationship can be created using Primary key-Foreign key relationship. In the One-to-Many Relationship in SQL, for example, a book can have multiple authors. A Many-to-Many (M:N) relationship is defined as a relationship between two tables where many rows from one table can have multiple matching rows in another table. Neither table can support a foreign key to relate the tables, so a junction table (aka join table or associative entity) is created. A junction table is a database table that contains foreign key references to two or more other database tables. It is the standard way of creating a many-to-many relationship between tables."

How do you prevent SQL injection?

"Option #1: Use of Prepared Statements (Parameterized Queries) Option #2: Use of Stored Procedures Option #3: Escaping all User Supplied Input Also Enforce: Least Privilege Also Perform: White List Input Validation"

What is a cursor?

"Oracle creates a memory area, known as context area, for processing an SQL statement, which contains all information needed for processing the statement, for example, number of rows processed, etc. A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set." You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: Implicit cursors and Explicit cursors

"What does REPLACE do? Use an example"

"Replace a subset in a column with a replacement, for all elements that apply. SELECT REPLACE( COLUMN_A, 'existing', 'replaced') from TABLENAME."

What does the DISTINCT keyword do. Use it in an example.

"Returns the unique records from a query. Select distinct * from tablename."

Give me an example of a case statement that would write 'A' for numbers less than 1000, 'B' for numbers greater than 1000, and 'C' otherwise.

"SELECT A,B, CASE WHEN C < 1000 Then 'A' WHEN C > 1000 THEN 'B' ELSE 'C' as 'C' FROM TABLE "

JOIN vs UNION

"SQL JOIN allows us to "lookup" records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names. UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables. "

What is an aggregate function?

"SQL aggregate functions return a single value, calculated from values in a column. •AVG() - Returns the average value •COUNT() - Returns the number of rows •FIRST() - Returns the first value •LAST() - Returns the last value •MAX() - Returns the largest value •MIN() - Returns the smallest value •SUM() - Returns the sum"

What is a scalar function?

"SQL scalar functions return a single value, based on the input value. •UCASE() - Converts a field to upper case •LCASE() - Converts a field to lower case •MID() - Extract characters from a text field •LEN() - Returns the length of a text field •ROUND() - Rounds a numeric field to the number of decimals specified •NOW() - Returns the current system date and time •FORMAT() - Formats how a field is to be displayed"

Why do we use stored procedures?

"Security: Provides advanced database functionality for users who wouldn't normally have access to these tasks. Faster execution: Stored procedures are parsed and optimized as soon as they are created and then stored in the database memory. This means that they execute a lot faster than sending many lines of SQL code from your application. Reduced network traffic: If you send many lines of SQL code over the network to your database server, this will have an impact on network performance. Reusable: You can write a stored procedure once, then call it from multiple places in your application."

"Compare flags If category is 'AAA' then flag is 'F' If category is 'AA' and value is <= 2000 then flag is 'G' If category is 'AA' and value is > 2000 then flag is 'H' If not any of these then flag is 'J' Compare to flag to expected flag Use a case statement to generate different flag values as expected_flag and compare to actual flag column"

"Select Category, CValue, CASE WHEN Category = 'AAA' THEN 'F' WHEN Category = 'AA' AND Cvalue <= 2000 THEN 'G' WHEN Category = 'AA' AND Cvalue > 2000 THEN 'H' ELSE 'J' END as ""Flag"" FROM TABLENAME"

How do you add a column to the table?

"The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE table_name ADD column_name datatype"

What is the difference between DELETE, TRUNCATE, and DROP?

"The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. " The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

How do you add a record to a table?

"The INSERT statement. The basic syntax to insert a value for each and every column is: INSERT INTO TABLE VALUES( ___ , ___ , ___ , ___ ) To insert only specific columns and leave others null: INSERT INTO TABLE(col1, col2) VALUES( ___ , ___ )"

What is the difference between Order By and Group By?

"The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default (ASC). To sort the records in a descending order, you can use the DESC keyword. The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns."

What is the purpose of SELECT INTO?

"The SELECT INTO statement selects data from one table and inserts it into a new table. SELECT * INTO new_table FROM table1"

What is the WHERE clause? What is the purpose of AND and OR?

"The SQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied then only it returns specific rows from the table. You would use WHERE clause to filter the records and fetching only necessary records. The WHERE clause is not only used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc." "The SQL AND and OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement." "SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...OR [conditionN];"

What is a trigger?

"Triggers are stored programs in PL/SQL, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events: A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers could be defined on the table, view, schema, or database with which the event is associated. "

"How would you combine two columns into two column? Use it in an example."

"Use the CONCAT command. Select CONCAT(A, B) as ""Combination"" from TABLENAME"

How do you drop a table?

"Using the DROP TABLE statement. It removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE. DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . | schema_name . ] table_name "

"What statement would you use to grab all data from a string column, where the value includes the string: ""red""? What would the query look like?"

"You would use the LIKE command. SELECT * FROM TABLE WHERE ColumnName like '%red%';"

Consider tables a and b with loan number. There are 5 rows in common, 10 total rows in a, 20 total rows in b. If a left outer join is performed what would be the output

10 rows that all belong to a

How about a right outer join

20 rows that all belong to b

How about a full outer join?

25 rows all together

What is a nested query (or subquery)?

A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

What is a cross join?

A cross join does not have a WHERE or ON clause, therefore it produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

What is a foreign key?

A foreign key is a column (or columns) that references a column (most often the primary key) of another table. This is to establish a relationship between records in separate tables.

What is a primary key?

A primary key is a column or set of columns that uniquely identify each row of a table. A driver's license number, a VIN number, or UPC (universal product code) are example of a primary key. A primary key must be unique and not null.

What is recursive query and how do you write recursive sql?

A. A query that somewhat calls itself, B. You start with the keyword "with", which lets you define a CTE, a common table expression. This a temp. named result set that's derived from a single select, insert, delete, or update statement. Afterwards, you call the CTE you defined, and establish a base case, or anchor query , and union all the result with the recursive query, which is a query that inner joins the CTE and the table it was derived from.

We have a table without primary key. How do you add a column that is a primary key and auto increamenting?

ALTER table tablename ADD ColumnID INTEGER PK AUTOINCREAMENT

What's an index?

An index can be created in a table to find data more quickly and efficiently. Indexes allow the database application to find data fast; without reading the whole table. The users cannot see the indexes, they are just used to speed up searches/queries. Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns that will be frequently searched against. You can use indexes or unique indexes (which require a unique value).

What languages make up SQL?

DDL, DML, DCL - data definition language, data manipulation language, and data control language

What's an ERD?

Entity Relationship Diagram: A Data modeling technique that illustrates the relationship between tables and represent the entity framework infrastructure.

What is the difference between WHERE and HAVING?

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. WHERE cannot be used to apply restrictions to an aggregate function.

How would use IN and EXIST? What is the difference?

IN works best for a small finite set of data, whereas EXISTS is better for subqueries. In most cases, EXISTS will be much more efficient (and faster) than an IN statement. When using an IN combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole, matching up based on the relationship specified for the IN. With an EXISTS or a JOIN, the database will return true/false while checking the relationship specified. Unless the table in the subquery is very small, EXISTS or JOIN will perform much better than IN.

What is an implicit cursor?

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

What is a view?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Can you have more than one primary key in one table? Can a primary key contain more than one column?

No. A table can have one and only one primary key constraint. However, a primary key can contain more than one column. A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. Uniqueness is only guaranteed when the columns are combined; when taken individually the columns do not guarantee uniqueness.

How to go about unifying two table that have a similar structure, except one column is different in data type?

One method is to cast the int into a string. Or if the string represents a number, you could cast the string into an int

What is an orphan?

Orphaned records are records that reference a key which no longer exists in the foreign table. If referential integrity is enforced by using a foreign key constraint, this cannot happen.

What is referential integrity?

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

How would you select the top 10% of rows from a table in SQL?

SELECT TOP 10% * FROM TABLENAME;

How would you write a query to grab the first 'X' amount of records?

SELECT TOP X FROM TABLENAME;

What are some of the String manipulation methods in SQL?

SUBSTRING, TRIM, LTRIM, RTRIM, REVERSE, REPLACE, LENGTH, CONCAT, UCASE, LCASE, etc.

What is UNION, UNION ALL, MINUS, INTERSECT? In these statements, what conditions must be met?

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows. The INTERSECT operator returns only those rows returned by both queries.The MINUS operator returns only unique rows returned by the first query but not by the second. The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character).

What is the difference between INNER JOIN and OUTER JOIN?

The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate. An outer join will also return rows that are not common between both tables.

Let's say we have two tables and they have columns 1, 2, 3, 4, and 5 in common. How to select these columns from both tables without losing data?

Union All

How can you retrieve rows from a database table?

Using a SELECT statement (aka query). The basic syntax is: "SELECT [ column_list | * ] [FROM table] [JOIN table ON join_predicate] ... [WHERE where_condition] [GROUP BY {col_name} [ASC | DESC]] [HAVING condition] [ORDER BY {col_name} [ASC | DESC]]"

What is GRANT and REVOKE?

You can grant users various privileges to tables and revoke those privileges. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL. GRANT privileges ON object TO user; REVOKE privileges ON object FROM user;

When would you want to use case statement in SQL?

You would want to use the case statement to give meaningful descriptions of cryptic values and/or specify the translation of the column

How do you add a record to a table?

insert into table_name(col1, col2, ...)

Difference between Procedure and Functions

procedure can return multiple values. function only returns one value

In order to perform a sum of loan amounts with respect to loan number, write a simple example of SQL statement

select loan_number, sum(loan_amount) from table_a group by loan_number