Kerala Plus Two Computer Science Notes Chapter 9 Structured Query Language
Most of the relational database management system like MySQL, Oracle, Sybase, Informix, Postgres, SQL Server and MS Access use SQL as standard database language.
Structured Query Language (SQL)
It is a language designed for managing data in relational database management system (RDBMS). It also provides an easy and efficient way to interact with relational database. The original version of SQL was developed by Donald D. Chamberlin and Raymond F. Boyce. It was originally called Structured English Query Language. (Sequel) and later renamed as SQL.
- Features of SQL
- The main features of SQL are:
- It is a relational database language, not a programming language.
- It is simple, flexible and powerful.
- It provides commands to create and modify tables, insert data into tables, manipulate data in the tables etc.
- It gives guidelines to major popular RDBMS to perform database operations.
- SQL is a nonprocedural language.
- SQL provides facility to add or remove different types of access permissions to users and also provide data security.
- It provides the concept of views.
Components of SQL
SQL has three components. They are:
1. Data Definition Language (DDL): It provides, commands to deal with the schema (structure) definition of the RDBMS. These are used to create, modify and remove database objects. The common DDL commands are CREATE, ALTER and DROP.
2.Data Manipulation Language (DML):
It provides commands for data manipulations. DML is a component of SQL that enhances efficient user interaction with the data system by providing a set of commands. The common DML commands are SELECT, INSERT, UPDATE and DELETE.
3.Data Control Language (DCL):
It is used to control access to the database, which 1 is very essential to a database system with respect to security concerns. The commands are GRANT and REVOKE. GRANT allows access privileges to the users to the database. REVOKE withdraws user’s access privileges given by using GRANT commands.
Working of MySQL
Features of MySQL are:
- MySQL is released under an opensource license. So it is customizable, It provides high security to the database.
- It is portable as it works on many operating systems and with many languages in eluding PHP, PERL, C, C++, JAVA, etc.
- MySQL works rapidly and effectively even with large volume of data.
- It is highly compatible with PHP.
1. Opening MySQL: In Ubuntu Linux, to open the Terminal window using,
Applications → Accessories → Terminal In Windows OS,
Start → Programs → MySQL → MySQL Server(version number) → MySQL command line, client
2. Creating a database: To create a database in MySQL, we use CREATE DATA
BASE command. Its syntax is:
CREATE DATABASE <database_name>;
3. Opening database: To perform operations on a database, we have to open it explicitly. MySQL gives a command USE to open a database. The syntax is:
USE <database_name>; eg., USE school;
The SHOW DATABASE command is used to check whether a database exists or not. It will list the entire databases in our system. The syntax is:
Data types in SQL
Data types defines the type of value that may be entered in the column of a table. It ensures the correctness of the data. MySQL data types are classifieds into three. They are:
1. Numeric data types: It can be any normal number. The most commonly used numeric data types are:
- INT or INTEGER: Integers are whole numbers without a fractional part. Thay can be positive, zero or negative, eg., 58, o, 11.
- DEC or DECIMAL: These are numbers with fractional part. Its standard form is DECIMAL (size, D) or DEC (size, D). The size indicates the total number of digits the value contains including decimal part and D represents the number of digits after the decimal point.
2. String (Text) data types: It is a group of characters. They are:
i. CHAR or CHARACTER: Character includes letters, digits, special symbols, etc. The CHAR is a fixed length character data types. Its syntax is,
where x is the maximum number of characters. x can be o to 255.
If the number of characters in the data is less than the declared size of the column, the remaining character positions in the string will be filled with white spaces.
ii. VARCHAR (size): It represents variable length string. It is similar to CHAR, but the space allocated for the data depends only on the actual size of the string, not on the declared size of the column. It did not append spaces with the values when they are stored. The length of the string can vary from 0 to 65535.
3. Date and Time data types: It is used ! to store date and time,
- DATE: MySQL represents date values in YYYYMMDD format. The supported range is from
- TIME: It shows values in the standard HH:MM:SS format. It can be used to store a specific point in time as well as an interval of time between two points in time that may > sometimes be larger than 23 hours.
SQL provides commands to perform different operations on database. The commands are DDL, DML and DCL. The commands under this category are:
1. Creating tables: The DDL command CREATE TABLE is used to define a table. Its syntax is,
CREATE TABLE <table_name> (<column_name> <data_type> [cconstr aint>] [,<column_namexdata_type> [<constraint>, ]....................);
Here <table_name> represents the name of the table, <column_name> represents the name of the column, <data_type> represents the type of data in the column, <constraint> specifies the rules that we can on the values of a column.
eg., CREATE TABLE student (adm_no INT, name VARCHAR (25), gender CHAR, dob DATE, course VARCHAR (15));
- Rules for naming tables and columns:
- The name may contain letters (AZ, az), digits (o 9), underscore (_) and $ (dollar) symbol.
- The name must contain at least one character.
- The name must not contain white spaces, special symbols.
- The name must not be an SQL keyword.
- The name should not duplicate with the names of other tables in the same database and with other columns in the same table.
These are rules enforced on data that are entered into the column of a table. When we create a table, we can apply constraints on the values that can be entered into its fields. The constraints ensure database integrity and hence they are: often called database integrity constraints, Constraints could be column level or table level.
a. Column constraints: These are applied only to individual columns. They are written immediately after the data type of the column.
- NOT NULL: It specifies that a column can never have NULL values. NULL is a keyword that represents an empty value. Two < NULL values cannot be added, subtracted and compared.
- AUTO_INCREMENT: It performs an auto-increment feature. By default, the starting value for this constraint island will be incremented by 1 for each new record. Only one AUTO_INCREMENT column per table is allowed.
- UNIQUE: It ensures that no two rows have the same value in the column specified with the constraint.
- PRIMARY KEY: It declares a column as the primary key of the table. It is similar to UNIQUE except that it can be applied only to one column or combination of columns. The primary keys cannot contain NULL values.
- DEFAULT: A default value can be set for a column, in case the user does not provide a value for that column of a record.
b. Table constraints: These are used not only on individual columns, but also on a group of columns. It appears at the end of the table definition.
2. Viewing the structure of a table: The DESCRIBE command is used to display the structure definitions of a table. Syntax is,
DESCRIBE <table_name>; OR DESC <table_name>; eg., DESC student;
3. Inserting data into table The DML command INSERT INTO is used to insert tuples into tables. The syntax is,
INSERT INTO <table_name>[<columnt>, <column2>,..., <columnN>] VALUES (<valuei>, <value2>, , <valueN>); eg., INSERT INTO student VALUES (1001, ‘Anu’, ‘F’, ‘2000/12/18’, ‘Science’ );
MySQL allows inserting several rows into a table with a single INSERT command by specifying multiple value lists. The general form is,
INSERT INTO <table_name>VALUES (....), ( ),..................... ; eg., INSERT INTO student (adm_no, name, gender, dob, course) VALUES (1002, ‘Mithra’, ‘F’, ‘2001/01/05’, ‘Science’), (1003, ‘Rahul’, ‘M’, ‘2003/10/07’, ‘Humanities’);
4. Retrieving information from tables:
The command SELECT is used for this purpose. It is used to retrieve information from specified columns in a table. The SELECT command has several forms of its own. Its simplest form is,
SELECT <column_name>[, <column_name>, <column_name> ] FROM <table_name>; eg., SELECT name, course FROM student;
If we want to display the entire column values of a table, we can use asterisk (*) symbol.
eg., SELECT * FROM student;
5. Eliminating duplicate values in columns: Duplication can be eliminated using the keyword DISTINCT.
eg., SELECT DISTINCT course FROM student;
If the column used with DISTINCT keyword contains more than one NULL value, only one will be shown in the result. If we give the keyword ALL in the place of DISTINCT, then the result will contain all the duplicate values in the column.
6. Selecting specific rows: It can be done by using WHERE clause. Its syntax is:
SELECT <column_name>[, <column_ name>, <column_name>, ] FROM <table_name> WHERE <condition>; eg., SELECT * FROM student WHERE gender = ‘F’;
The conditions are expressed with the help of relational operators and logical operators. SQL has a set of special operators I for setting conditions. These include:
a. Condition based on a range of values can be given as condition. BETWEEN.. AND is used to specify the range. It allows specifying a range of values which belong to either numeric or date or time data type.
eg., SELECT name, income FROM student WHERE income BETWEEN 20000 AND I 40000;
b. Condition based on a list of values:
The values may be of any data type, but it should match with those of the column used I in the condition. In such a case, the operator IN is used with the list,
eg., SELECT * FROM student WHERE course IN (‘Commerce’, ‘Science’); SELECT * FROM student WHERE course NOT IN (‘Science’);
c. Condition based on pattern matching: SQL provides a pattern matching operator LIKE for this purpose. Patterns are specified using two characters; % (percentage) matches a substring of characters and _ (underscore) matches single character, Patterns are case sensitive, eg., SELECT name FROM student WHERE name LIKE ‘%ar’;
Here the query gives a list of names in the table that end with the substring ‘ar’.
d. Conditions based on NULL value search: It can be achieved with the help of l IS operator. The condition will be true when the specified column contains a NULL value in the records.
eg., SELECT name, course FROM student WHERE income IS NULL;
7. Sorting: The result of a query can be sorted in the ascending or descending order by making use of ORDER BY clause. The: order is to be specified by using the keyword ASC or DESC along with the column name. By default, the display will be in the ascending order.
eg., SELECT * FROM student ORDER BY name;
Multiple sorting can be performed after selection with the help of ORDER BY clause.
eg., SELECT name, course FROM student ORDER BY course, name;
MySQL provides a number of built-in functions that can be applied to all rows in a table or to a subset of the table specified by WHERE clause. These functions are called aggregate functions because they operate on aggregate of tuples (records). The result of this function is a single value. These are:
SUM(): Total of the values in the column,
AVG(): Average of the values in the col; umn.
MIN(): Smallest value in the column.
MAX():Largest value in the column.
COUNT(): Number of non NULL values in the column.
eg., SELECT MAX (income), MIN (income), AVG (income) FROM student;
8. Grouping of records: The rows of a table can be grouped together based on a com; mon value using the GROUP BY clause, Tuples with the same value in the attribute specified in the GROUP BY clause are placed. together in one group. Thus different groups are formed based on distinct values in the column. So this process can be considered as categorization of records.
eg., SELECT course, COUNT (*), AVG (in ' come) FROM student ; GROUP BY course;
9. Applying conditions to form groups:
We can apply conditions to form groups S with the help of HAVING clause. It is used to form groups of records.
eg., SELECT course, COUNT (*), FROM student ; GROUP BY course HAVING COUNT (*) > 3;
10. Modifying data in tables:
The DML command UPDATE is used to change the data in table. It changes the values in one or more columns of specified rows. These changes may be affected in all or only in selected rows of the table. The new data for the column within these rows is given using the keyword SET, which is essential clause of UPDATE command. The syntax is:
UPDATE ctable name> SET <column_name> = <value>[, <column_ name> = <value>,] [WHERE S <condition>]; eg., UPDATE student SET income = 25000 WHERE name = Anand’;
11. Changing the structure of a table: SQL provides a DDL command ALTER;) TABLE to modify the structure of a table.
a. Adding a new column: One or more columns can be added at any position in an existing table using ADD clause. Its syntax is:
ALTER TABLE <table_name> ADD<column_name> <data_type> [<size>] [<constraint>] [FIRST AFTER : <column_name>]; eg., ALTER TABLE student ADD marks INTEGER AFTER dob, ; ADD reg_no INTEGER; :
b. Changing the definition of a column: We can done this using MODIFY clause with ALTER TABLE command. Its syntax is:
ALTER TABLE <table_name> MODIFY < column_name> <data_type> [<size>] [<constraint>];. eg., ALTER TABLE student MODIFY reg_no INTEGER UNIQUE;
c. Removing column from a table: If we want to remove an existing column from a table, we can use DROP clause along with ALTER TABLE. The syntax is:
ALTER TABLE <table„name> DROP<column_name>; eg., ALTER TABLE student DROP marks;
d. Renaming a table: We can rename a table using RENAME TO clause with ALTER TABLE command. Its syntax is:
ALTER TABLE <table_name> RENAME TO <new_table_name>; eg., ALTER TABLE student RENAME TO student20i5;
12. Deleting rows from a table:
The DML command DELETE is used to remove individual or a set of rows from a table. The rows which are to be deleted are selected by using the WHERE clause. If the WHERE clause is not used, all the rows in the table will be deleted. The syntax is,
DELETE FROM <table_name> [WHERE <condition>]; eg., DELETE FROM student20i5 WHERE red_no = 1110;
13. Removing table from a database:
DROP TABLE command removes a table from the database permanently even though the table contains tuples. Once data is lost, it cannot be recovered later. Its syntax is:
DROP TABLE <table_name>; eg., DROP TABLE student20is;
14. Nested queries: Nested query means one query inside another. A MySQL inner query is also called subquery, while the query that contains the subquery is called an outer query. SQL first evaluates the inner query within the WHERE clause and the result of inner query is then substituted in the condition of the outer query. While using relational operators, ensure that the subquery (inner query) returns a single row output.
eg., SELECT name, course FROM student WHERE income=(SELECT MAX (income) FROM student);
15. Concept of views:
MySQL supports the concept of views, which is feature of RDBMS. A view is a virtual table that does not really exist in the database but is derived from one or more tables. The table(s) from which the tuples are collected to constitute a view is called base table(s). These tuples are not physically stored anywhere, rather the definition of the view is stored in the database. A view can be created with the DDL command CREATE VIEW.
The syn tax is:
CREATE VIEW <view_name> } AS SELECT <column, name1> [<column_ name2>, .....................] FROM <table_name> [WHERE <condition>]; eg., CREATE VIEW student 2000 AS SELECT * FROM student 2015 WHERE dob < ‘200112’;
Without sparing extra storage space, we can use the same table as different tables (but virtual).
The views implement sharing along with privacy.
It also helps to reduce’ the complexity of conditions with WHERE clause while retrieving, updating or deleting records from tables.
If a view is no longer needed, it can be removed from the database with a DROP VIEW command. But it will not affect the base table(s). The syntax is,
DROP VIEW <view_name>; ; eg., DROP VIEW student2014;