More Advanced SQL Statements

In this third part to a series on beginning SQL, you’ll learn how to use SQL statements to manipulate entire tables: to join them, alter them, and even delete them. It’s all part of keeping a firm grip on your databases. Keep reading to learn more.

Wedding, matrimony. Holy bliss. These are the things we think of when we discuss forming a union or joining together. We think of babies when we talk about creation. Then we look at the blank computer screen in front of us and realize that we should have spent more time working and less time talking. If you don’t finish up those database queries, you’ll never get to those bars. And without bars full of drunken women whose eyes are too bleary to notice your odd resemblance to Woody Allen, you’ll never get married.

When working with databases, sometimes you want to wed data from two or more tables. After all, that’s what relational databases are all about. Well to do that, you are going to need a number of SQL Statements designed specifically for that. You’ll also need an Internet degree in religion and a certificate that says you can marry people. No, just kidding; the statements will do the trick.

So without further ado, let’s get these tables married and make some babies…err more tables.

JOIN

Now let’s create a second table that shows our employees’ birth dates. We’ll also have employees’ names and employees’ social security numbers in the table. We’ll name the table Birthdays. After you finish creating the Birthdays table, go back to your Employees table and add the SocialSecurity column. When you are done the two tables should appear this way:

Employees Table

First Name

Last Name

Position

Salary

Social Security

Larry

Smith

President

$100,000

555001234

Homer

Sampson

CEO

$75,000

444001234

Homer

Sampson

CFO

$75,000

333001234

Liza

Sampson

CTO

$80,000

222001234

Bruce

Lee

Security

$50,000

11100123

Your

Name

Whipping Boy

$15,000

00000000


Birthdays Table

Employee Name

Birth Date

Social Security

Larry Smith

02/12/1932

555001234

Homer Sampson

02/17/1950

444001234

Homer Sampson

02/17/1950

333001234

Liza Sampson

06/19/1990

222001234

Bruce Lee

06/06/06

11100123

Your Name

01/01/1992

00000000

So now we want to take these two tables, have them make sweet sweet database love, and create one new table. This new table will contain each employee’s first name, birth date, and social security number.

 

SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES, BIRTHDAYS

WHERE EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;

This will result in the following table:


First Name

Birth Date

Larry

02/12/1932

Homer

02/17/1950

Homer

02/17/1950

Liza

06/19/1990

Bruce

06/06/06

You

01/01/1992

If we wanted to know who in the database was born on 06/06/06 we could add an AND operator to the mix and find out.


SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES, BIRTHDAYS

WHERE EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity

AND BIRTHDAYS.BirthDate=06/06/06;

That would show us that Bruce (and the Antichrist if he were in our database) was born on that day.


First Name

Birth Date

Bruce

06/06/06

The above examples are ways of retrieving data from two tables using primary keys. The other way to do so is with a JOIN statement. There are technically three types of JOIN STATEMENTS. We will begin with the INNER JOIN.


SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES

INNER JOIN BIRTHDAYS

ON EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;

The INNER JOIN will return all rows of data from the EMPLOYEE table, and all the data from the BIRTHDAYS table where there is a match. If there is not a match, those rows will not be shown.

With the LEFT JOIN, it is slightly different. The LEFT JOIN will return all rows of data from the EMPLOYEE table, and all the data from the BIRTHDAYS table, even if there were no matches in the BIRTHDAYS table. Basically what this means is that if you forgot to type in Bruce Lee’s birth date, it will still list his name, but his birth date field will be blank.


SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES

LEFT JOIN BIRTHDAYS

ON EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;

Finally there is the RIGHT JOIN, which works in a manner opposite to that of the LEFT JOIN, in that it will return all the results in the BIRTHDAY table regardless of whether there is a match, yet will not list the data in the EMPLOYEES table if there is no match. Its code looks like this:


SELECT EMPLOYEES.FirstName, BIRTHDAYS.BirthDate

FROM EMPLOYEES

RIGHT JOIN BIRTHDAYS

ON EMPLOYEES.SocialSecurity=BIRTHDAYS.SocialSecurity;


{mospagebreak title=UNION STATEMENT}

The UNION Statement is similar to the JOIN statement, with the exception that with the UNION statement, the data types must be the same and it will only display unique values.


SELECT SocialSecurity FROM EMPLOYEES

UNION

SELECT SocialSecurity FROM BIRTHDAYS;

This would give us a list of social security numbers in each table. Since all are the same in this example, it would be pointless to do this. However, if you had a list of employees from two companies with social security numbers, you could wed the two together to create an entire list of social security numbers.

If we wanted to see a list of all social security numbers, even though they would be duplicates, we would use the UNION ALL statement.

 

SELECT SocialSecurity FROM EMPLOYEES

UNION ALL

SELECT SocialSecurity FROM BIRTHDAYS;

Altering Tables

Now that you have made your table, you suddenly decide you want to add some columns and maybe even drop some columns. The ALTER statement allows us to do exactly that.

Let’s say we want to add a column called IQ to our table (they’ve decided to give people salaries based on their intelligence quotient). We’ll need to know the data-type of the column first, in this case, integer.


ALTER TABLE EMPLOYEE ADD IQ Integer(30);

This will add the IQ column to the table.

Now after the president sees the great dip in his salary because of his low IQ, he may ask you (the new the top earner in the company) to delete that column you just made.

Not a problem. Well. Aside from the impending dip in your salary.


ALTER TABLE EMPLOYEE DROP COLUMN IQ;

{mospagebreak title=And along came baby}

Now that we’ve wedded some tables in unholy matrimony and made some babies, let’s add to our amazing powers and learn the CREATE statement.

With the CREATE statement you can create new tables and even an entirely new database. Before we do however, let’s discuss data types.

In short, the data type defines what sort of data a column can hold. If you signify that a field is a date field, and try to enter text into that field, your database will yell at you. Everything has a place, and put the proper things there. And close the door. What, were you born in a barn?

The following table shows the different data types available.

Data Type

Function

CHAR

For character strings with a fixed-length

VARCHAR

For character strings with variable lengths

INT

For integer numbers

Numeric

For holding numbers with decimals

Float

For numbers with floating points

Date

For dates

Time

For times

TIMESTAMP

For dates and times

INTERVAL

For holding time intervals

There are other forms of data types, but they are beyond the scope of this article. For the time being, these should suffice.

So now your boss wants a new database. This one will show the employees’ names and how long they have been with the company. We will call this database Seniority. To create the database in SQL, do the following:

 

CREATE DATABASE SENIORITY;

It’s just that simple. Or is it? Of course it isn’t. We also need a table for our database. So let’s create that now.


CREATE TABLE SENIORITY

(

EmployeeName VARCHAR,

YearsEmployed INT

)


{mospagebreak title=Speeding Up the Search}

Databases, like programmers, can get big and bloated. When you get them to try and give you something, it takes them forever. If they get too big, they simply freeze up and then how will you get your data?

Well the solution is simple: create an index. An index cuts the time it takes to run a query by, you guessed it…indexing the data held within the database.


CREATE UNIQUE INDEX MyIndex

ON EMPLOYEE (FirstName);


The above code creates a UNIQUE INDEX called MyIndex. It indexes the data based on the FirstName column found within the EMPLOYEE table. A UNIQUE INDEX simply means that it allows no duplicate records.

For a SIMPLE INDEX (an index that allows duplicates) use the following code:

CREATE INDEX MyIndex

ON EMPLOYEE (FirstName);

If you wanted to add a sort feature you could do that as well.

 

CREATE UNIQUE INDEX MyIndex

ON EMPLOYEE (FirstName DESC);

And if you want to index more than one column, that too is possible.


CREATE UNIQUE INDEX MyIndex

ON EMPLOYEE (FirstName, LastName);

{mospagebreak title=OOPS I DID IT AGAIN}

Just as easy as creating tables and databases is, so too is deleting them.


DELETE TABLE EMPLOYEE;

Or to delete a database


DELETE DATABASE EmployeeDatabase;

And finally, to delete the data within a table, but not the table itself, use the TRUNCATE statement.


TRUNCATE TABLE EMPLOYEE;

Well you are now on your way to creating formative relationships with a myriad of eager databases. While I haven’t taught you everything there is to know about SQL (you still need to know functions and a plethora of other things), this should get you started.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort