Type of SQL and Various Database Query

By ukmodak | June 6th 2022 12:23:03 PM | viewed 57 times

Type of SQL

SQL statements are divided into five different categories:

  1. Data definition language (DDL)
  2. Data manipulation language (DML)
  3. Data Control Language (DCL)
  4. Transaction Control Statement (TCS)
  5. Session Control Statements (SCS)

Data Definition Language (DDL) Command and Query for Different Database Type

SQL Type MySql Query MS Sql Query Oracle Query MongoBD Query
DDL(Data Definition Language)
create table
create index
		CREATE TABLE CUSTOMERS(
		   ID INT  NOT NULL AUTO_INCREMENT,
		   NAME VARCHAR (20) NOT NULL UNIQUE,
		   AGE  INT DEFAULT '0',
		   ADDRESS  TEXT ,
		   SALARY  DECIMAL (18, 2),
           person_id int CHECK(person_id > 0),
           is_active tinyint default 0 CHECK(is_active in(0,1)),		   
		   PRIMARY KEY (ID),
		   CONSTRAINT FK_customer_person FOREIGN KEY (person_id) REFERENCES Person(id)
        );
			
       
CREATE INDEX/UNIQUE INDEX name_index ON person (name,mobile);  
drop table/DATABASE
drop index
DROP TABLE CUSTOMERS;
DROP DATABASE SQL_TEST; 
DROP INDEX person.name_index  
DROP INDEX name_index ON person 
DROP INDEX name_index 
alter table
alter procedure
ALTER TABLE person ADD is_active tinyint default 0;
ALTER TABLE person MODIFY COLUMN/ALTER COLUMN/MODIFY name tinyint default 0;
ALTER TABLE person DROP COLUMN is_active;
TRUNCATE
(TRUNCATE command removes all the records from a table. But this command will not destroy the table's structure)
TRUNCATE TABLE table_name
COMMENT
RENAME
RENAME TABLE old_table_name to new_table_name
DML(Data Manipulation Language)
SELECT
INSERT
INSERT INTO student VALUES(101, 'Adam', 15);
or
INSERT INTO student(id, name) values(102, 'Alex');
UPDATE
 UPDATE student SET name='Abhi', age=17 where s_id=103; 
DELETE
DELETE FROM student;
or
DELETE FROM student WHERE s_id=103;
DCL(Data Control Language)

Data Control Language(DCL) is used to control privileges in Database. To perform any operation in the database, such as for creating tables, sequences or views, a user needs privileges. Privileges are of two types

    In DCL we have two commands
  • System: This includes permissions for creating session, table, etc and all types of other system privileges.
  • Object: This includes permissions for any command or query to perform any operation on the database tables.
  • GRANT: Used to provide any user access privileges or other priviliges for the database.
  • REVOKE: Used to take back permissions from any user.
		GRANT CREATE SESSION TO username;
		GRANT CREATE TABLE TO username;
		ALTER USER username QUOTA UNLIMITED ON SYSTEM;
		GRANT sysdba TO username
		GRANT CREATE ANY TABLE TO username
		GRANT DROP ANY TABLE TO username
		REVOKE CREATE TABLE FROM username
	
TCL(transaction Control Language)
COMMIT
COMMIT command is used to permanently save any transaction into the database.
ROLLBACK
This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.
SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.
		INSERT INTO class VALUES(5, 'Rahul');
		COMMIT;
		UPDATE class SET name = 'Abhijit' WHERE id = '5';
		SAVEPOINT A;
		INSERT INTO class VALUES(6, 'Chris');
		SAVEPOINT B;
		INSERT INTO class VALUES(7, 'Bravo');
		SAVEPOINT C;
		SELECT * FROM class;
		ROLLBACK TO B;
		SELECT * FROM class;
		ROLLBACK TO A;
		SELECT * FROM class;
	
analyze
EXPLAIN/ANALYZE SELECT * FROM CUSTOMERS; 
bONEandALL
Visitor

Total : 9222

Today :7

Today Visit Country :

  • United States