SQL - DML

SQL to insert data into a table

insert into books (isbn, title, authors) values ('978-0-596-00920-5', 'Head First Java', 'Kathy Sierra, Bert Bates');

SQL to select data based on a column value

select * from books where title='Head First Java';

SQL - DDL

This post contains a collection of SQL DDL (Data Definition Language) statements.

DDL or Data Definition Language consists of SQL statements which create, drop, or alter a table.

Adding a column in a table:

alter table books add column author varchar(100);

Dropping a column from a table:

alter table books drop column author;

b

MySql Commands

Command to show all database schema in your application

show databases;

Command to switch to a particular database

use bookshop;

Command to show all the tables in a databse (you will have to issue an appropriate 'use' command to switch to that database)

show tables;

Normalization

A database in the 1st normal form does not have multivalued columns like the one below.

Name | PhoneNumber

John | 122345, 456879

In a database which is already in the 1st normal form, if all columns are functional dependent on the whole key, then the database is said to be in the 2nd normal form.

In a database, if all the columns are directly dependent on the whole key then the database is said to be in the 3rd normal form.

Transactions

Transactions in SQL provide the ACI of ACID properties in relational databases.

A - Atomocity

Atomicity means all or nothing. Imagine, we have an application for a bank, and we need to implement the logic for a transfer of money from one account to another account. This procedure will reduce the balance in one account and increase the balance in another account. We have to make sure that either both these steps happen or none happen. It would be very undesirable if we reduced the balance of one account without increasing the balance of the other. If both these sql statements are executed in a transaction then we are guaranteed that either both will be executed or none will be executed, because transactions guarantee Atomicity.

C - Consistency

Imagine a forum application where we have a table for a post, and a table for user such that the post table has a foreign key for the user. What would happen if we delete a use who has several entries in the post table? We will end up with rows in the post table which no longer refer to a valid user. This violates a constraint in the post table, leaving the database in an inconsistent state. Well in an RDBMS this will not happen because it guarantees consistency.

I - Isolation

Isolation means the results of a transactions are invisible to another transaction until that transaction completes.

Complex SQL queries

In simple SQL queries we usually query one or multiple tables, and possibly with different types of joins.

The notion of a complex query is rather arbitrary, some people would consider any query with a join to be complex. However for this discussion we consider a complex query, one in which we need to group results, perform some aggregations, etc.

Imagine we have a database containing tables for books and authors. We want to find the number of authors for each book. We can write this query with a GroupBy clause and the COUNT aggregate function.

SELECT Book.title,         COUNT(*) AS Authors     FROM  Book JOIN Book_author        ON Book.isbn = Book_author.isbn     GROUP BY Book.title;

Now let's look at another query which is a bit more complex than the previous query.

select users.user_id,         users.email,         count(*) as how_many,        max(classified_ads.posted) as how_recent from users, classified_ads where users.user_id = classified_ads.user_id group by users.user_id, users.email order by how_recent desc, how_many desc;

The query above joins the users table with the classified_ads table. It selects user_id and email from users, while joining the user_id field in users and classified_ads tables. So basically the result is going to contain the user_id and email of all users who are in the classified_ads table (basically those users who have published classified ads). The group by users.user_id, users.email clause specifies that we want to bundle together all rows which have the same values in the users.user_id and users.email column. What this means is that all these rows will be represented as one row. Representing multiple rows as one row would in make cases make sense only if we run some aggregate function on these rows. In the above case, we use two aggregate functions count, and max. The count will compute the number of rows clumped by due to the group by clause. The max aggregate has been specified on the classified_ads.posted field. What this will do is compute the row which has the maximum value in the classified_ads.posted field. The order by clause specifies the order in which the rows will be listed. The above sql specifies that we want to order by how_recent desc, which will display the most recent classified ad posting at the top. For those rows which have the same how_recent value, the rows will be ordered by the how_many computed value. One thing to remember here is that most databases will record timestamps with a subsecond precision. So two dates showing the same data (upto day level precision) may not be the same in reality.

Having:

The where clause works on rows, whereas the having clause works on groups.

Simple SQL queries

The following sql will give us the number of rows in the user table:

select count(*) from users;

The following sql will tell us the schema of the user table:

describe users

A simple query from one table will typically have the following structure:

• the select list (which columns in our report)
• the name of the table
• the where clauses (which rows we want to see)
• the order by clauses (how we want the rows arranged)

select email from users where email like '%ail.com';

select email from users where email like '%ail.com' order by email;
select count(*) from users where registration_date >= '1998-09-01' and registration_date < '1998-10-01' and email like '%mit.edu';
select user_id, email  from users  where 0 < (select count(*)             from classified_ads            where classified_ads.user_id = users.user_id);
select user_id, email  from users  where exists (select 1               from classified_ads               where classified_ads.user_id = users.user_id);
AND A JOIN:
select users.user_id, users.email, classified_ads.posted from users, classified_ads where users.user_id = classified_ads.user_id order by users.email, posted;
The above query will not show users who have not posted any classifieds. 
If we want to see users who have not posted any classifieds, then we need an outer join.
select users.user_id, users.email, classified_ads.posted from users, classified_ads where users.user_id = classified_ads.user_id(+) order by users.email, posted;

Extending a simple query into a JOIN

Suppose that you have a query from one table returning almost everything that you need, except for one column that's in another table. Here's a way to develop the JOIN without risking breaking your application:

1. add the new table to your FROM clause
2. add a WHERE constraint to prevent Oracle from building a Cartesian product
3. hunt for ambiguous column names in the SELECT list and other portions of the query; prefix these with table names if necessary
4. test that you've not broken anything in your zeal to add additional info
5. add a new column to the SELECT list