Structured Query Language(SQL)

This Blog will help you revise your database management system(DBMS) Important concept of(SQL)

DDL DATA DEFINITION LANGUAGE create, alter, rename, truncate, drop

DQL DATA QUERY LANGUAGE select

DML DATA MANIPULATION LANGUAGE insert, update& delete

DCL DATA CONTROL LANGUAGE grant, revoke, permissions

TCL TRANSACTION CONTROL LANGUAGE start transaction, commit, rollback

1. create database (name );

2. use (database name );

*** Let's say, we name the table as a student

3. create table student( column_name datatype constraint);

EXAMPLE:

create table stu(id int primary key, name varchar(80), age int not null);

insert into stu values(1,'pinak', 34);

insert into stu values(2,'monika', 30);

insert into stu values(3,'neha', 40);

insert into stu values(4,'charvi', 34);

select * from stu;

1. insert into student(values);

SQL language is case insensitive so mentioning insert or INSERT will not give that much difference;

*values for the string should be mentioned in " ".

* After writing the query, if you want to execute it then end it with;

5. order by to check layout use query desc student;

6. to check the table (columns and rows) use the query select * from the student;

1. There are so many data types (char(20/any numeric value), varchar(any numerical value), blob(int value), int, tinyint , double, boolean, float, date) for strings we should use

varchar >> char (because of memory space)

1. create database if not exist (name of database);

   create database if not exists college5;

   drop database if exists college5;

   Good practice

   Drop database if exists (name of database);

   1. show tables;

   2. show database;

CONCEPT OF KEYS -->

PRIMARY KEY

is the one key that is unique and not null --> you have to mention it as id int primary key, id int unique in a given table

Foreign key It can be more than one in a table and can be null, it is the primary key from another table -->foreign key(cust_id) references customer(id)

Cardinality--> No of tuples/unique/rows

Attributes --> values in the table

Degree--> No of columns

Constraints--.> You can use constraints as

1. id int primary key/unique/not null/foreign key(cust_id) references customer(id)/ default 2400);

2. age int check (age >=18)

13.where clause select col1,col2, from table_name where conditions;

-->select * from student where marks >180; in where --> arithmetic --> comparison -->logical --> bitwise

14. Betweenselects range

1. In matches any list

2. Not to negate

   select * from emp3 where marks between 80 and 89;

   select from emp3 where city in ("lud", "jal");

   select * from emp3 where city not in ("lud", "jal") ;

3. Limitclause-- select * from the student (where marks >30 limit clause>3;

4. .order by clause order by city asc/desc;

5. Aggregate functions These functions perform a calculation on a set of values, return a single value; select avg (marks) from the student;--> count()/min()/max()/avg()/sum()

   select city, avg(marks) from emp3 group by city order by avg(marks) desc;

6. group by Generally we use group by with some aggregate functions

   select max(marks) from emp3;

   select city, name, count(rollno) from emp3 group by city,name;

   select city, avg(marks) from emp3 group by city order by avg(marks) desc;

7. distinct keyword --> select distinct city from the student;

   select distinct city from emp3;

8. having clause we can apply it after grouping

9. General Order

   select column from table_name

   where condition

   group by column

   having the condition

   order by column asc;

10. update (to change any value/attribute value in the table)

    set SQL_SAFE_UPDATES=0;

update student set grade="O" where grade="A";

1. delete

   (to delete existing row) where condition;

   --> delete from student where marks>33;

2. For foreign key we use cascading update cascade and delete cascade

3. alter

   (to change the schema)

   --> ADD column --> DROP column -->RENAME table -->MODIFY column(modify datatype/constraint)

   -->alter table payment

   add column age int not null default 99;

4. Truncate

   (to delete table data)

   truncate table table_name

   truncate table payment;

5. Views

   (virtual base on the result set of SQL statements )

   VIEWS -->

   create view view1

   as select roll no, name from student;

   select * from view1;

   drop view view1;

6. SQL Subqueries

   select column from table name where col_name(operator) (subquery);

7. JOINS

   Inner Join

   example: you have A and B are tables which means common columns

Left Join

Only A(on the left side and common type of columns )

Rigth Join only B(on right side and common type of columns )

Full JoinAll columns are shown that are in the left and right

Rigth Exclusive Join only B part, not a common one

Left Exclusive Joinonly A part, not a common one

Self Join(for pairing with one table itself)

Union --> Left Join Union Rigth Join

EXAMPLE1: joins

create table student(id int primary key, name varchar(80));

insert into student(id,name) values (100,"pinu"), (102, "bob"),(103, "casey");

create table coarse(id int primary key, coarse varchar(30));

insert into coarse (id,coarse) values(102,"english"), (103,"maths"), (105,"english"); select * from student left join coarse on student.id=coarse.id;

EXAMPLE2: foreignkey

create table dept2(id int primary key, name varchar(70));

insert into dept2 values (101,'english');

insert into dept2 values (102,'english');

select * from dept2;

create table teacher2(id int primary key, name varchar(30), dept_id2 int, foreign key(dept_id2) references dept2(id)

on update cascade

on delete cascade);

insert into teacher2 values (101,'pinak', 101);

insert into teacher2 values (102,'charvi',102);

select * from teacher2;