Although we refer to the SQL language as a “query language,” it can do much more than just query a database. It can define the structure of the data, modify data in the database, and specify security constraints.
The SQL language has several parts:
Data-definition language :The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.
Data-manipulation language :The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.
Integrity :The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed
View definition :The SQL DDL includes commands for defining views
Transaction control :SQL includes commands for specifying the beginning and ending of transactions
Embedded SQL and dynamic SQL :Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, and Java.
Authorization :The SQL DDL includes commands for specifying access rights to relations and views.
The set of relations (Tables) in a database must be specified to the system by means of a data-definition language (DDL). The SQL DDL allows specification of not only a set of relations (Tables), but also information about each relation (Tables), including:
The schema for each relation.
The types of values associated with each attribute.
The integrity constraints.
The set of indices to be maintained for each relation.
The security and authorization information for each relation.
The physical storage structure of each relation on disk.
char(n) : A fixed-length character string with user-specified length n. The full form, character , can be used instead
varchar(n) : A variable-length character string with user-specified maximum length n. The full form, character varying,is equivalent
int : An integer (a finite subset of the integers that is machine dependent). The full form, integer, is equivalent.
smallint : A small integer (a machine-dependent subset of the integer type)
numeric(p,d) : A fixed-point number with user-specified precision. The number consists of p digits (plus a sign), and d of the p digits are to the right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but neither 444.5 or 0.32 can be stored exactly in a field of this type. En
real, double precision : Floating-point and double-precision floating-point numbers with machine-dependent precision.
float(n) : A floating-point number, with precision of at least 'n' digits.
Additional Information :
Each type may include a special value called the null value. A null value indicates an absent value that may exist but be unknown or that may not exist at all.
The char data type stores fixed length strings. Consider, for example, an attribute A of type char(10). If we store a string "Avi" in this attribute, 7 spaces are appended to the string to make it 10 characters long. In contrast, if attribute B were of type varchar(10), and we store "Avi" in attribute B, no spaces would be added.
When comparing two values of type char, if they are of different lengths extra spaces are automatically added to the shorter one to make them the same size, before comparison.
When comparing a char type with a varchar type, one may expect extra spaces to be added to the varchar type to make the lengths equal, before comparison; however, this may or may not be done, depending on the database system. As a result, even if the same value “Avi ” is stored in the attributes A and B above, a comparison A = B may return false. We recommend you always use the varchar type instead of the char type to avoid these problems.
In some databases, SQL also provides the nvarchar type to store multilingual data using the Unicode representation
create table :
create table department
(dept_name varchar(20),
(building varchar(20),
(budget numeric(12,2),
primary key (dept_name));
Semicolon shown at the end of the command may be optional for some databases.
SQL supports a number of different integrity constraints:
Primary-key : The primary key attributes are required to be nonnull and unique; that is, no tuple can have a null value for a primary-key attribute, and no two tuples in the relation can be equal on all the primary-key attributes. Although the primary-key specification is optional, it is generally a good idea to specify a primary key for each relation.
primary key (course id, sec id, semester, year)
Foreign key - references s: Theforeign key specification says that the values of attributes for any tuple in the relation must correspond to values of the primary key attributes of some tuple in relation 's'.
foreign key (course id, sec id, semester, year) references section
not null : The not null constraint on an attribute specifies that the null value is not allowed for that attribute; in other words, the constraint excludes the null value from the domain of that attribute.
SQL prevents any update to the database that violates an integrity constraint.
insert into :
insert into instructor values (10211, ’Smith’, ’Biology’, 66000);
delete from : would delete all tuples from the student relation. Other forms of the delete command allow specific tuples to be deleted
delete from student;
drop table : deletes all information about the dropped relation from the database. The 'delete' command retains relation (table) 'r', but deletes all tuples (rows) in 'r'. The 'drop' command deletes not only all tuples of 'r', but also the schema for 'r'. After 'r' is dropped, no tuples can be inserted into 'r' unless it is re-created with the create table command.
drop table r;
alter table : command to add attributes to an existing relation. All tuples in the relation are assigned null as the value for the new attribute. In below query, 'r' is the name of an existing relation, 'A' is the name of the attribute to be added, and 'D' is the type of the added attribute. We can drop attributes from a relation by the command
alter table r add A D;
alter table r drop A;
The basic structure of an SQL query consists of three clauses: select, from, and where. The query takes as its input the relations listed in the from clause, operates on them as specified in the where and select clauses, and then produces a relation (tables) as the result.
Q. Find the names of all instructors.
| name |
|---|
| Pranav |
| Abhishek |
| Satish |
| Ravi |
| Ajeet |
select name
from instructor;
Q. Find the names of all departments in which instructors are working by removing duplicates.
| Dept_names |
|---|
| IT |
| EXTC |
| CMPN |
| IT |
| ETRX |
select distinct Dept_names
from instructor;
Q. Find the names of all departments in which instructors are working without removing duplicates.
| Dept_names |
|---|
| IT |
| EXTC |
| CMPN |
| IT |
| ETRX |
select all Dept_names
from instructor;
Q. The select clause may also contain arithmetic expressions involving the operators +, −, ∗, and / operating on constants or attributes of tuples. . Demonstrate such a query on table below
| Salary |
|---|
| 1000 |
| 2000 |
| 3000 |
| 5000 |
| 10000 |
select salary *1.1
from instructor;
The above query returns a relation that is the same as the instructor relation, except that the attribute salary is multiplied by 1.1. This shows what would result if we gave a 10% raise to each instructor; However, that it does not result in any change to the instructor relation.
Q. “Find the names of all instructors in the Computer Science department who have salary greater than $70,000."
| name | Salary |
|---|---|
| Pranav | 1000 |
| Ajeet | 22000 |
| Satish | 322000 |
| Jayesh | 5000 |
| Som | 70000 |
select name
from instructor
where salary > 70000;
SQL allows the use of the logical connectives and, or, not in the where clause. The operands of the logical connectives can be expressions involving the comparison operators <, ≤, >, ≥, =,and <>. SQL allows us to use the comparison operators to compare strings and arithmetic expressions, as well as special types, such as date types
where deptname = ’Comp. Sci.’ and salary > 70000;
Q: Retrieve the names of all instructors, along with their department names and department building name.
Suppose we have the department name from the attribute deptname, but the department building name is present in the attribute building of the relation department.
department(dept_name, building, budget)
instructor(ID, name, dept_name, salary)
To answer the query, each tuple in the instructor relation must be matched with the tuple in the department relation whose deptname value matches the deptname value of the instructor tuple.
select name, instructor.deptname, building
from instructor, department
where instructor.deptname= department.dept name;
Q. For all instructors in the university who have taught some course, find their names and the course ID of all courses they taught.
teaches(ID, course_id, sec_id, semester, year)
instructor(ID, name, dept_name, salary)
Ans :
select name, course id
from instructor, teaches
where instructor.ID= teaches.ID;
Note that the above query outputs only instructors who have taught some course. Instructors who have not taught any course are not output;
Q. To find instructor names and course identifiers for instructors in the Computer Science department
teaches(ID, course_id, sec_id, semester, year)
instructor(ID, name, dept_name, salary)
Ans :
select name, course id
from instructor, teaches
where instructor.ID= teaches.ID; and instructor.deptname = ’Comp.Sci.’;
Note that since the dept name attribute occurs only in the instructor relation, we could have used just deptname, instead of instructor.dept name in the above query.
The natural join operation operates on two relations and produces a relation as the result.
It considers only those pairs of tuples with the same value on those attributes that appear in the schemas of both relations.
So, computing instructor natural join teaches considers only those pairs of tuples where both the tuple from instructor and the tuple from teaches have the same value on the common attribute, ID
The result relation has only those tuples, that give information about an instructor and a course that that instructor actually teaches.
We do not repeat those attributes that appear in the schemas of both relations; rather they appear only once.
The order in which the attributes are listed: first the attributes common to the schemas of both relations, second those attributes unique to the schema of the first relation, and finally, those attributes unique to the schema of the second relation.
select name, courseid
from instructor natural join teaches;
Q. List the names of instructors along with the the titles of courses that they teach.
course (course_id, title, dept_name, credits)
teaches (ID, course_id, sec_id, semester, year)
instructor (ID, name, dept_name, salary)
select name, title
from instructor natural join teaches, course
where teaches.courseid = course.courseid;
The natural join of instructor and teaches is first computed and then a Cartesian product of this result with course is computed, from which the where clause extracts only those tuples where the course identifier (ID) from the join result matches the course identifier (course_id) from the course relation.
Second Method using Natural Join:
select name, title
from (instructor natural join teaches) join course using (courseid);