1.) What are the statements that can query, define, and modify tables?
it is easy to create and modify tables, constraints, and indexes in Design view,
and to create relationships by using the Relationships window. Alternatively, you can create and modify these same entities
by writing data-definition queries in SQL view.
A data definition language or data
description language (DDL) is a syntax similar to a
computer programming language for
defining data structures,
especially database schemas.
Unlike other queries, a data-definition query
does not retrieve data. Most queries use Data Manipulation Language (DML),
which comprises Structured Query Language (SQL) commands that specify how data
from existing database objects is combined and manipulated to produce the data
results that you want. Data-definition queries use Data Definition Language
(DDL), which comprises SQL commands that specify the definition of database
objects that store or index data, and SQL commands that control user access to
database objects.
Create or modify a table
CREATE TABLE table_name
(field1 type [(size)] [NOT NULL] [index1]
[, field2 type [(size)] [NOT NULL] [index2]
[, ...][, CONSTRAINT constraint1 [, ...]])
1. On the Message Bar, click Options, and
then click Enable this content.
1. On the Create tab, in the Other group,
click Query Design.
3. Close the Show Table dialog box.
3. On the Design tab, in the Query Type group,
click Data Definition.
5. Type the following SQL statement:
6. On the Design tab, in the Results group,
click Run.
To create a table, you use a CREATE TABLE command. A CREATE
TABLE command has the following syntax:
The only required elements of a CREATE TABLE command are the
CREATE TABLE command itself, the name of the table, at least one field, and the
data type of each field. Let us look at a simple example.
Suppose that you want to create a table to store the name, year,
and the price of used cars that you are considering for purchase. You want to
allow up to 30 characters for the name, and 4 characters for the year. To use a
data-definition query to create the table, do the following:
NOTE You must first enable the contents of the database
in order for a data definition query to run.
The design grid is hidden, and the SQL view
object tab is displayed.
CREATE TABLE Cars (Name TEXT(30), Year
TEXT(4), Price CURRENCY)
CREATE TABLE statement
A commonly used CREATE command is the CREATE TABLE command.
The typical usage is:
CREATE [TEMPORARY] TABLE [table name] ( [column
definitions] ) [table parameters].
column definitions: A comma-separated list consisting of any of the following
·
Column definition: [column
name] [data type] {NULL | NOT NULL} {column
options}
·
Primary key definition: PRIMARY
KEY ( [comma separated column list] )
·
Constraints: {CONSTRAINT} [constraint
definition]
·
RDBMS specific
functionality
For example, the command to create a table
named employees with a few sample columns would be:
CREATE TABLE employees (
id
INTEGER PRIMARY KEY,
first_name VARCHAR(50) NULL,
last_name VARCHAR(75) NOT NULL,
dateofbirth DATE NULL
);
Note that some forms of CREATE TABLE DDL
may incorporate DML (data
manipulation language)-like constructs as well, such as the CREATE
TABLE AS SELECT (CTAS) syntax of SQL.
Modify a table
To modify a table, you use an ALTER TABLE command. You can use
an ALTER TABLE command to add, modify, or drop (remove) columns or constraints.
An ALTER TABLE command has the following syntax:
ALTER TABLE table_name predicate
where predicate can be any of the following:
ADD COLUMN field type[(size)]
[NOT NULL] [CONSTRAINT constraint]
ADD CONSTRAINT multifield_constraint
ALTER COLUMN field type[(size)]
DROP COLUMN field
DROP CONSTRAINT constraint
Suppose that you want to add a 10-character text field to store
information about the condition of each car. You can do the following:
1. On the Create tab, in the Other group,
click Query Design.
2. Close the Show Table dialog box.
3. On the Design tab, in the Query Type group,
click Data Definition.
The design grid is hidden, and the SQL view
object tab is displayed.
4. Type the following SQL statement:
ALTER TABLE Cars ADD COLUMN Condition TEXT(10)
5. On the Design tab, in the Results group,
click Run.
To create an index on
an existing table, you use a CREATE INDEX command. A CREATE INDEX command has
the following syntax:
CREATE
[UNIQUE] INDEX index_name
ON table (field1 [DESC][, field2
[DESC], ...])
[WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]
The only required
elements are the CREATE INDEX command, the name of the index, the ON argument,
the name of the table that contains the fields that you want to index, and the
list of fields to be included in the index.
·
The DESC argument
causes the index to be created in descending order, which can be useful if you
frequently run queries that look for top values for the indexed field, or that
sort the indexed field in descending order. By default, an index is created in
ascending order.
·
The WITH PRIMARY
argument establishes the indexed field or fields as the primary
key of the table.
·
The WITH DISALLOW NULL
argument causes the index to require that a value be entered for the indexed
field — that is, null values are not allowed.
Suppose that you have
a table named Cars with fields that store the name, year, price, and condition
of used cars that you are considering for purchase. Also suppose that the table
has become large and that you frequently include the year field in queries. You
can create an index on the Year field to help your queries return results more
quickly by using the following procedure:
1.
On the Create tab,
in the Other group, click Query Design.
2.
Close the Show
Table dialog box.
3.
On the Design tab,
in the Query Type group, click Data Definition.
The design grid is
hidden, and the SQL view object tab is displayed.
4.
Type the following SQL
statement:
CREATE
INDEX YearIndex ON Cars (Year)
5.
On the Design tab,
in the Results group, click Run.
A
constraint establishes a logical condition that a field or combination of
fields must meet when values are inserted. For example, a UNIQUE constraint
prevents the constrained field from accepting a value that would duplicate an
existing value for the field.
A
relationship is a type of constraint that refers to the values of a field or
combination of fields in another table to determine whether a value can be
inserted in the constrained field or combination of fields.
To
create a constraint, you use a CONSTRAINT clause in a CREATE TABLE or ALTER
TABLE command. There are two kinds of CONSTRAINT clauses: one for creating a
constraint on a single field, and another for creating a constraint on multiple
fields.
Single-field
constraints
A
single-field CONSTRAINT clause immediately follows the definition of the field
that it constrains, and has the following syntax:
CONSTRAINT constraint_name {PRIMARY KEY
| UNIQUE | NOT NULL |
REFERENCES foreign_table [(foreign_field)]
[ON
UPDATE {CASCADE | SET NULL}]
[ON
DELETE {CASCADE | SET NULL}]}
Suppose
that you have a table named Cars with fields that store the name, year, price,
and condition of used cars that you are considering for purchase. Also suppose
that you frequently forget to input a value for the car's condition, and that
you always want to record this information. You can create a constraint on the
Condition field that prevents you from leaving the field empty, by using the
following procedure:
1.
On the Create tab,
in the Other group, click Query Design.
2.
Close the Show
Table dialog box.
3.
On the Design tab,
in the Query Type group, click Data Definition.
The design grid is hidden, and the SQL view
object tab is displayed.
4.
Type the following SQL
statement:
ALTER TABLE Cars ALTER COLUMN Condition TEXT
CONSTRAINT ConditionRequired NOT NULL
5.
On the Design tab,
in the Results group, click Run.
Now
suppose that, after a while, you notice that there are many similar values in
the Condition field that should be the same. For example, some of the cars have
a Condition value of poor and others have a value of bad.
After you clean up the values so that they are more consistent, you could
create a table, named CarCondition, with one field, named Condition, that
contains all of the values that you want to use for the condition of cars:
1.
On the Create tab,
in the Other group, click Query Design.
2.
Close the Show
Table dialog box.
3.
On the Design tab,
in the Query Type group, click Data Definition.
The design grid is hidden, and the SQL view
object tab is displayed.
4.
Type the following SQL
statement:
CREATE TABLE CarCondition (Condition TEXT(10))
5.
On the Design tab,
in the Results group, click Run.
6.
To insert the values
from the Condition field of the Cars table into the new CarCondition table,
type the following SQL into the SQL view object tab:
INSERT INTO CarCondition SELECT DISTINCT
Condition FROM Cars;
NOTE The
SQL statement in this step is an append query. Unlike a data-definition
query, an append query ends with a semicolon.
7.
On the Design tab,
in the Results group, click Run.
To
require that any new value inserted in the Condition field of the Cars table
matches a value of the Condition field in the CarCondition table, you can then
create a relationship between CarCondition and Cars on the field named
Condition, by using the following procedure:
1.
On the Create tab,
in the Other group, click Query Design.
2.
Close the Show
Table dialog box.
3.
On the Design tab,
in the Query Type group, click Data Definition.
The design grid is hidden, and the SQL view
object tab is displayed.
4.
Type the following SQL
statement:
ALTER TABLE Cars ALTER COLUMN Condition TEXT
CONSTRAINT FKeyCondition REFERENCES CarCondition (Condition)
5.
On the Design tab,
in the Results group, click Run.
Multiple-field
constraints
A
multiple-field CONSTRAINT clause can be used only outside of a field-definition
clause, and has the following syntax:
CONSTRAINT constraint_name
{PRIMARY KEY (pk_field1[, pk_field2[,
...]]) |
UNIQUE
(unique1[, unique2[, ...]]) |
NOT
NULL (notnull1[, notnull2[, ...]]) |
FOREIGN
KEY [NO INDEX] (ref_field1[, ref_field2[, ...]])
REFERENCES foreign_table
[(fk_field1[,
fk_field2[, ...]])] |
[ON
UPDATE {CASCADE | SET NULL}]
[ON
DELETE {CASCADE | SET NULL}]}
Let
us look at another example using the Cars table. Suppose that you want to
ensure that no two records in the Cars table have the same set of values for
Name, Year, Condition, and Price. You could create a UNIQUE constraint that
applies to these fields, by using the following procedure:
1.
On the Create tab,
in the Other group, click Query Design.
2.
Close the Show
Table dialog box.
3.
On the Design tab,
in the Query Type group, click Data Definition.
The design grid is hidden, and the SQL view
object tab is displayed.
4.
Type the following SQL
statement:
ALTER TABLE Cars ADD CONSTRAINT NoDupes UNIQUE
(name, year, condition, price)
5. On the Design tab, in the Results group,
click Run.
Many data description languages use a
declarative syntax to define fields and data types. SQL, however, uses a
collection of imperative verbs whose effect is to modify the schema of the
database by adding, changing, or deleting definitions of tables or other
objects. These statements can be freely mixed with other SQL statements, so the
DDL is not truly a separate language.
CREATE statements
Create - To make a new
database, table, index, or stored procedure.
A CREATE statement in SQL creates
an object in a relational
database management system (RDBMS). In the SQL 1992
specification, the types of objects that can be created are schemas, tables, views,
domains, character sets, collations, translations, and assertions. Many
implementations extend the syntax to allow creation of additional objects, such
as indexes and
user profiles. Some systems (such as PostgreSQL) allow CREATE, and other DDL
commands, inside a transaction and
thus they may be rolled back.
DROP statements
Drop - To destroy an
existing database, table, index, or view.
A DROP statement in SQL removes
an object from a relational
database management system (RDBMS). The types of objects that
can be dropped depends on which RDBMS is being used, but most support the
dropping of tables, users,
and databases. Some systems (such as PostgreSQL) allow DROP and other DDL
commands to occur inside of a transaction and
thus be rolled back.
The typical usage is simply:
DROP objecttype objectname.
For example, the command to drop a table
named employees would be:
DROP TABLE employees;
The DROP statement is distinct from
the DELETE and TRUNCATE statements, in that DELETE and TRUNCATE do
not remove the table itself. For example, a DELETE statement might
delete some (or all) data from a table while leaving the table itself in the
database, whereas a DROP statement would remove the entire table from
the database.
ALTER statements
Alter - To modify an
existing database object.
An ALTER statement in SQL changes
the properties of an object inside of a relational
database management system (RDBMS). The types of objects that
can be altered depends on which RDBMS is being used. The typical usage is:
ALTER objecttype objectname parameters.
For example, the command to add (then remove) a
column named bubbles for an existing table named sink would
be:
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;
Referential integrity statements
Finally, another kind of DDL sentence in SQL is
one used to define referential integrity relationships,
usually implemented as primary key and foreign key tags in some columns of the
tables.
These two statements can be included inside
a CREATE TABLE or an ALTER TABLE sentence.
2.) How is Relational Algebra related to SQL?
SQL is the most common way to express relational algebra on a computer.
The Structured Query Language (SQL) is the common language of most database software such as MySql, Postgresql, Oracle, DB2, etc. This language translates the relational theory into practice but imperfectly, SQL is a language that is a loose implementation of relational theory and has been further modified in its actual implementation by the Relational Database Management System (RDBMS) software that uses it.
Translating an arbitrary SQL query into a logical query plan (i.e., a relational algebra expression) is a complex task. In these course notes we try
to explain the most important elements of this translation.
3.) Give some sample SQL queries that is used in a real scenario, e.g., student registration system
- “Get all the courses taught by CS professors”
σProfId=Professor.Id And Professor.DeptId=‘CS0(Teaching)
-
An example
Given the following Person table,
Id Name Address Hobby
1123 John 123 Main St. Stamps
1123 John 123 Main St. Coin
5556 Mary 7 Lake Dr. Hike
9876 Bart 5 Pine St. Stamps
with the expression σHobby=0Stamps0(Person), we
will get the following table back
Id Name Address Hobby
1123 John 123 Main St. Stamps
9876 Bart 5 Pine St. Stamps
-An example
Given the following Person table,
Id Name Address Hobby
1123 John 123 Main St. Stamps
9876 Bart 5 Pine St. Stamps
with the expression πName,Hobby(Person), we
will get the following table back
Name Hobby
John Stamps
John Coin
Mary Hike
Bart Stamps
0 comments :
Post a Comment