Search 75,065 tutors
FIND TUTORS

Working With DDL and DML

1.     Overview: Working with what?!

SQL  statements are divided into two languages: DDL and DML. We will review the code contained in these languages and apply it to some database tables.

2.     Review of Relational Databases

Relational databases developed in 1970 by Dr. E.F. Codd. This type of database eliminated some of the problems that were associated with standard files and other database designs. 

Why do we need Relational Databases?

Many companies often start off collecting data by use of a spreadsheet like the one in the table below.


Customer

Number

Customer

Name

Order

Number

Order Date

Part

Number

Part

Description

Number

Ordered

Quoted

Price

Warehouse

Rep

Number

148

Al’s Appliance and Sport

21608

10/20/2013

AT94

Iron

11

$21.95

3

20

148

Al’s Appliance and Sport

21619

10/23/2013

DR93

Gas Range

1

$495.00

2

20

282

Brookings

Direct

21614

10/21/2013

KT03

Dishwasher

2

$595.00

3

35

356

Ferguson’s

21610

10/20/2013

DR93

Gas Range

1

$495.00

2

65

356

Ferguson’s

21610

10/20/2013

DW11

Washer

1

$399.99

3

65

408

The Everything

Shop

21613

10/21/2013

KL62

Dryer

4

$329.95

1

35

608

Johnson’s Dept

Store

21617

10/23/2013

BV06

Home Gym

2

$794.95

2

65

608

Johnson’s Dept

Store

21617

10/23/2013

CD52

Microwave

Oven

4

$150.00

1

65

608

Johnson’s Dept

Store

21617

10/23/2013

KV29

Treadmill

2

$1290.00

2

65

 

What they often find that when the company grows, all the data they collect is difficult to manage due to issues like data redundancy. Redundancy is the duplication of data in more than one place. Notice in the table above how the Customer number 608 appears three times for the same Order Number.

By using the relational model, you can reduce data redundancy, which saves disk storage and leads to efficient data retrieval. You can also view and manipulate data in a way that is both intuitive and efficient. 

 

How a Database Table is Organized

A relational database consists of tables. Tables consist of rows and columns, referred to as records and fields.  

A table is typically modeled after a real-world entity, such as a vendor or invoice. 

 Column represents some attribute of the entity, such as an amount of an invoice or the vendor’s address. 

A row contains a set of values for a single instance of the entity, such as one invoice or one vendor. Basically it’s one item on the list. 

Most tables have a primary key which uniquely identifies each row in the table. PK is typically a single column but can be made up of more than one column. 

A table can also be defined with one or more indexes. An index provides an efficient way to access data from a table based on the values in specific columns.             

3.     DDL – Data Definition Language

Data definition language lets you work with the objects in the database. Objects include database, tables, index, view, stored procedure, plus a few others . Typically used by database administrators (although there are exceptions). 

Statement

Description

CREATE DATABASE

Creates a new database

CREATE TABLE

Creates a new table in a database

CREATE INDEX

Creates a new index for a table

ALTER TABLE

Changes the structure of an existing table

DROP DATABASE

Drops an existing database

DROP TABLE

Drops an existing  table in a database

DROP INDEX

Drops an existing index for a table

 

4.     DML – Data Manipulation Language

Data manipulation lets you work with data in the database. Typically used by SQL programmers. 

Statement

Description

SELECT

Retrieves data from one or more tables

INSERT

Adds one or more new rows to a table

DELETE

Deletes one or more existing rows from a table

This is where we will focus our time for this lesson. Specifically with the SELECT statement.

SELECT Statement From a Single Table

Syntax Guide for the SELECT statement:

SELECT [ *| ALL | DISTINCT ] [TOP n [PERCENT] ] column_spec1 [[AS] result_column1,

column_spec2 [[AS] result_column2]

FROM table_1

 

Examples:

SELECT * FROM Invoices

SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal – PaymentTotal - CreditTotal

FROM Invoices

SELECT InvoiceNumber, InvoiceDate, InvoiceTotal AS Total,

InvoiceTotal – PaymentTotal – CreditTotal AS Balance

FROM Invoices

 

SELECT Statements to Retrieve Data From More Than One Table

Inner Joins

 

Syntax Guide for an INNER JOIN:

 

SELECT select_list

FROM table_1

 [ INNER ]   JOIN table_2

                ON join_condition_1

[ INNER ]   JOIN table_3

                ON join_condition_2

 

Example:

SELECT InvoiceNumber, VendorName

FROM Vendors JOIN Invoices

ON Vendors.VendorID = Invoices.VendorID

 

Outer Joins

There are three types: LEFT, RIGHT and FULL.

Syntax Guide for an OUTER JOIN:

SELECT select_list

FROM table_1

(LEFT|RIGHT|FULL)    [OUTER]    JOIN table_2

                ON join_condition_1

[LEFT|RIGHT|FULL)    [OUTER]    JOIN table_3

                ON join_condition_2]…

 

LEFT OUTER JOINS

When you use a left outer join, the result set includes all the rows from the first (or left) table. 

A SELECT statement that uses a left outer join:

                SELECT VendorName, InvoiceNumber, InvoiceTotal

                FROM Vendors LEFT JOIN Invoices

                                ON Vendors.VendorID = Invoices.VendorID

 

 

RIGHT OUTER JOINS

Similarly, when you use a right outer join, the result set includes all the rows from the second (or right table.

Example:

SELECT VendorName, InvoiceNumber, InvoiceTotal

                FROM Invoices RIGHT JOIN Vendors

                                ON Invoices.VendorID = Vendors.VendorID

 

FULL OUTER JOINS

When you use a full outer join, the result set includes all the rows from both tables. 

Which Join Should I Use?

When coding Outer Joins, it’s a common practice to avoid RIGHT OUTER JOINS. You can do that by substituting a LEFT OUTER JOIN for a RIGHT OUTER JOIN and reversing the order of the tables in the FROM clause, using the left keyword for the RIGHT keyword. This often makes it easier to read statements that join more than two tables. 

 

Ashburn SQL tutors