Download to read offline
Relational Database Language.pptx
- 1. Relational database language Prepared By Ms.Sheethal Aji Mani Assistant Professor Kristu Jayanti College
- 2. Basic DataTypes • Number • Char • Varchar and Varchar2 • Date and Time
- 3. DDL(Data DefinitionLanguage) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. • DDL stands for Data Definition Language. • It is a language used for defining and modifying the data and its structure. • It is used to build and modify the structure of your tables and other objects in the database. SQLs data definition language (DDL) defines information about each relation in a database which is listed below in detail: • SQLs DDL specifies the schema of each relation i.e. the logical design of each relation which states the name of that relation, attributes it carry and also specifies the domain of those attributes. • SQL DDL specifies the integrity constraint which makes sure that any changes made to the database don’t affect the consistency of data. • SQL DDL also maintains the set of indices for each relation which let you retrieve the records from the database quickly. • SQL DDL maintains the information about the security of data in the database and it also keeps the information regarding the authorization for each relation in the database. • SQL DDL also describes the storage structure of each relation on the hard disk.
- 4. Examples ofDDL commands: • CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers). • DROP – is used to delete objects from the database. • ALTER-is used to alter the structure of the database. • TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed. • COMMENT –is used to add comments to the data dictionary. • RENAME –is used to rename an object existing in the database.
- 5. 1. CREATE COMMAND •CREATE command is used for creating objects in the database. • It creates a new table. Syntax: CREATE TABLE <table_name> ( column_name1 datatype, column_name2 datatype, . . . column_name_n datatype ); Example : CREATE command CREATE TABLE employee ( empid INT, ename CHAR, age INT, city CHAR(25), phone_no VARCHAR(20) );
- 6. 2. DROP COMMAND •DROP command allows to remove entire database objects from the database. • It removes entire data structure from the database. • It deletes a table, index or view. Syntax: DROP TABLE <table_name>; OR DROP DATABASE <database_name>; Example : DROP Command DROP TABLE employee; OR DROP DATABASE employees; • If you want to remove individual records, then use DELETE command of the DML statement.
- 7. 3. ALTER COMMAND •An ALTER command allows to alter or modify the structure of the database. • It modifies an existing database object. • Using this command, you can add additional column, drop existing column and even change the data type of columns. Syntax: ALTER TABLE <table_name> ADD <column_name datatype>; OR ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name>; OR ALTER TABLE <table_name> DROP COLUMN <column_name>; Example : ALTER Command ALTER TABLE employee ADD (address varchar2(50)); OR ALTER TABLE employee CHANGE (phone_no) (contact_no); OR ALTER TABLE employee DROP COLUMN age; To view the changed structure of table, use 'DESCRIBE' command. For example: DESCRIBE TABLE employee;
- 8. 4. RENAME COMMAND •RENAME command is used to rename an object. • It renames a database table. Syntax: RENAME TABLE <old_name> TO <new_name>; Example: RENAME TABLE emp TO employee;
- 9. 5. TRUNCATE COMMAND •TRUNCATE command is used to delete all the rows from the table permanently. • It removes all the records from a table, including all spaces allocated for the records. • This command is same as DELETE command, but TRUNCATE command does not generate any rollback data. Syntax: TRUNCATE TABLE <table_name>; Example: TRUNCATE TABLE employee;
- 10. Queries The SQLSELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets. Syntax If you want to fetch all the fields available in the field, then you can use the following syntax.
- 13. where clause TheSQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records. The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.
- 16. AND operator TheSQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement. The AND Operator The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
- 18.
- 19.
- 21.
- 22. Delete statement TheSQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.
- 24. View in SQL view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view. Views, which are a type of virtual tables allow users to do the following − • Structure data in a way that users or classes of users find natural or intuitive. • Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. • Summarize data from various tables which can be used to generate reports.
- 25.
- 28. General Constraints Constraints arethe rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.
- 29. Dropping Constraints Anyconstraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command.
- 30. Indexes Indexes arespecial lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
- 33. Additional Features • SQLis easy to learn. • SQL is used to access data from relational database management systems. • SQL can execute queries against the database. • SQL is used to describe the data. • SQL is used to define the data in the database and manipulate it when needed. • SQL is used to create and drop the database and table. • SQL is used to create a view, stored procedure, function in a database. • SQL allows users to set permissions on tables, procedures, and views.
- 34. Advantages of SQL There are the following advantages of SQL: High speed Using the SQL queries, the user can quickly and efficiently retrieve a large amount of records from a database. No coding needed In the standard SQL, it is very easy to manage the database system. It doesn't require a substantial amount of code to manage the database system. Well defined standards Long established are used by the SQL databases that are being used by ISO and ANSI. Portability SQL can be used in laptop, PCs, server and even some mobile phones. Interactive language SQL is a domain language used to communicate with the database. It is also used to receive answers to the complex questions in seconds. Multiple data view
- 35.