DDL and DML Example with Figures and SQL statements

 Example of Data-Manipulation Language

SQL is a nonprocedural query language. A query accepts numerous tables (maybe only one) as input and always returns a single table as output.

Fig. 01: Instructor table

Fig. 02: Department table

Here's an example of a SQL query that returns the names of all History department instructors.

select instructor.name from instructor where instructor dept_name = 'History';

The query indicates that rows from the table instructor with the department name History must be obtained and their name attributes presented. Executing this query produces a table with a single column named name and a collection of rows, each of which includes the name of an instructor whose dept.name is History. If you perform the query on the table in Figure 01, you will get two rows, one with the name ElSaid and the other with the name Califier.

Queries may include data from many tables. The following query, for example, returns the instructor ID and department name of all instructors affiliated with a department with a budget larger than $95,000. 

select instructor.ID, department.dept, name from instructor, department where instructor dept_name = department.dept name and department.budget > 95000;

NoteBook:The 'instructor.ID' means the calling process of a table field. We are calling the 'ID' field of the 'instructor' table. 

If the above query is executed on the data in Figures 01 and 02's tables, the system will discover that there are two departments with budgets of more than $95,000 - Computer Science and Finance - each with five instructors. As a consequence, the output will be a table with two columns (ID, dept name) and five rows: 

Fig. 03: Result of the query from instructor table

(12121, Finance), (45565, Computer Science), (10101, Computer Science), (83821, Computer Science), and (76543, Finance).

Example of Data-Manipulation Language

SQL has a comprehensive DDL that allows you to construct tables, integrity constraints, assertions, and so on. The department table, for example, is defined with the SQL DDL statement:

create table department 

    (dept_name char (20),

    building char (15), 

    budget numeric (12,2));

Fig. 04: Creating a table named 'department'

NoteBook: 'dept_name char (20)' means the dept_name field's length is limited to 20 characters. The dept_name can't be more than 20 lengths. Same meaning for the 'building char (15)'. The statement 'budget numeric (12,2)' means the budget allows up to and including 999 999 999 9.99 

When the preceding DDL statement is executed, it generates a department table with three columns: dept name, building, and budget, each of which has a unique data type. Some data is inserted in the 'depratment' table which is shown in Figure 05. Furthermore, the DDL statement alters the data dictionary, which contains metadata. A table schema is an example of metadata. 

Fig. 05: Department table with some information