Understanding Keys and SQL Commands in Database Management Systems
Explore different types of keys used in databases such as Candidate Key, Super Key, Composite Key, Primary Key, Alternate Key, Unique Key, and Foreign Key. Learn about SQL commands categorized into DDL, DML, and DCL and various query languages like Create, Alter, Rename, Drop, Comment, Truncate, Insert, Update, Delete, Merge, Lock table, Grant, and Revoke. Enhance your knowledge of database subject in RDBMS.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Different types of keys and languages used in database Subject RDBMS
There are mainly 7 types of Keys, that can be considered in a database: Candidate Key A set of attributes which can uniquely identify a table can be termed as a Candidate Key. A table can have more than one candidate key, and out of the chosen candidate keys, one key can be chosen as a Primary Key. In the above example, since EmployeeID, InsuranceNumber and PanNumber can uniquely identify every tuple, they would be considered as a Candidate Key. Super Key The set of attributes which can uniquely identify a tuple is known as Super Key. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn t true. Composite Key A composite key is a combination of two or more columns that identify each tuple uniquely. Here, the Employee_ID and Month-Year_Of_Salary can be grouped together to uniquely identify every tuple in the table.
Primary Key A set of attributes which are used to uniquely identify every tuple is also a primary key. In the above example, since EmployeeID, InsuranceNumber and PanNumber are candidate keys, any one of them can be chosen as a Primary Key. Here EmployeeID is chosen as the primary key. Alternate Key Alternate Keys are the candidate keys, which are not chosen as a Primary key. From the above example, the alternate keys are PanNumber and Insurance Number. Unique Key The unique key is similar to the primary key, but allows one NULL value in the column. Here the Insurance Number and the Pan Number can be considered as unique keys. Foreign Key An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers. in the above example, the Employee_ID from the Employee_Information Table is referred to the Employee_ID from the Employee_Salary Table.
Relational query languages Structured Query Language(SQL) as we all know is the database language by the use of whichwe can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the required tasks. These SQL commands are mainly categorized into four categories as: DDL Data Definition Language DML Data Manipulation Language DCL Data Control Language
Types of Query Languages Create Alter Rename Drop Comment Truncate DDL(Data definition language) Insert Update Delete Merge Lock table DML(Data manipulation language) Grant Revoke DCL(Data control language)
DDL DDL(Data Definition Language) : 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. Examples of DDLcommands: CREATE is used to create the database or its objects (like table, index, function, views, store procedure and triggers). Example: Create Table Student (Rank Int,StudentName varchar(50),Mark Float) DROP is used to delete objects from the database. ALTER-is used to alter the structure of the database. Example: Alter Table Student Add (StudentAddress varchar (100))
TRUNCATEis used to remove all records from a table, including all spaces allocated for the records are removed. Syntax: TRUNCATE TABLE table_name; table_name: Name of the table to be truncated. DATABASE name student_data COMMENT is used to add comments to the data dictionary. Comments can be written in the following three formats: 1. Single line comments: Comments starting and ending in a single line are considered as single line comments. Line starting with is Syntax:-- single line comment -- another comment SELECT * FROM Customers; a comment and will not be executed. 2.Multi line comments: Multi line comments: Comments starting in one line and ending in Syntax:/*multi line comment another different line are considered as multi line comments. comment */ SELECT * FROM Customers; 3. In line comments: In line comments: In line comments are an extension of multi line comments, comments can be stated in between the statements and are enclosed in between /* and */ . Syntax:SELECT * FROM /* Customers; */ RENAME is used to rename an object existing in the database. Syntax: ALTER TABLE table_name RENAME TO new_table_name
DML DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. Examples of DML: INSERT is used to insert data into a table. Example: Insert Into Student (Rank, StudentName, Mark) Values(1, Kumar ,450) UPDATE is used to update existing data within a table. Example: update student set StudentName= Manoj where StudentName= Kumar DELETE is used to delete records from a database table. Example: Delete from student where StudentName= Manoj
DCL DCL(Data Control Language) : DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system. Examples of DCL commands: GRANT-gives user s access privileges to database. Syntax: GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH GRANT OPTION]; REVOKE-withdraw user s access privileges given by using the GRANT command. Syntax: REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}
