SQL Programming


Misc

Q: What are some examples of data integrity repair or database cleaning?

A: deleting orphaned foreign key data; removing records marked for removal

Q: What are prepared statements?

A: representing literal values in SQL with parameters, and setting those parameters with separate calls

Q: What is SQL Injection?

A: Usually where a site has been poorly designed to directly copy user input (input fields) or querystring/form parameters into the text of a SQL statement, allowing them to cause the database to execute (for malicious reasons) SQL command (for example DELETE * FROM USERS). One of the main ways to guard against this is with prepared statements.

Q: What is the benefit of using prepared statements?

A: protects against SQL injection, can work around platform data type dependencies, can increase performance if query is used multiple times (because they are compiled).

Q: What are the benefits of stored procedures?

A: Performance. Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and invocation overhead.

Q: What is the difference between a table and a view?

A: A view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view.

Q: If you’re dumping SQL from a DB into a file (e.g. with mysqldump), what are some of the arguments you would typically set?

A: specify database and table name, separation character, fields are within quotes or not, include create table, include drop table

Joins

Q: Describe the difference between an INNER and OUTER join.

A: Inner joins show only the intersection, the set of rows having a shared primary key .  Outer joins include this plus the ‘empties’ where a primary key is found in only one of the two tables.

Q: What is the difference between a RIGHT INNER and LEFT INNER join?

A:  It’s a moot point, there’s only one kind of inner join.

Outer Joins        

Q: Write an example of a left outer join and explain what it does.

A:

SELECT foo.x, bar.x FROM foo LEFT OUTER JOIN bar ON foo.x = bar.x

Returns the left table plus nulls or matches from the right table

Q: Write an example of a right outer join and explain what it does.

A:

SELECT foo.x, bar.x FROM foo RIGHT OUTER JOIN bar ON foo.x = bar.x

Returns the right table plus nulls or matches from the left table

Group by

Assumes the use of an aggregator like SUM(), and the goal is to reduce to fewer rows of output than input.

Given this CUSTOMERS table (note the duplicate names):

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | kaushik  |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SELECT NAME, SUM(SALARY) FROM CUSTOMERS

     GROUP BY NAME;

...will give only 5 rows of output, one per name.

Indexes

Q: What is a database index?

A: An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

Q: What are the performance penalities of using Indexes?

A: (1) Slower insertion and deletion speed. Every time you alter a table the indexes must be updated accordingly. This is a serious concern for a table that will be updated frequently. (2) Space penalty: indexes take up space in memory or on disk.

Tip


Links marked with arrow icons ( or ) will open in a new tab.

You will see this message only once.