SQL Queries.rtf

{\rtf1\ansi\ansicpg1252\deff0\nouicompat\deflang1033{\fonttbl{\f0\fnil\fcharset0 Calibri;}}
{\*\generator Riched20 10.0.18362}\viewkind4\uc1 
\pard\sa200\sl276\slmult1\b\f0\fs40\lang9 sql Queries\par
\fs28 1)Data Definition Language (DDL)\par
\b0\fs22 used to define the database structure or schema.\par
\b i)Create table\b0\par
CREATE TABLE table_name(\par
Col_name1 datatype(),\par
Col_name2 datatype(),\'85\par
Col_namen datatype(),\par
);\par
\b ii)Alter- add,modify,change,rename,drop\par
a) add\b0\par
ALTER TABLE table_name ADD Col_name datatype()...;\par
\b b)modify \b0 -only change the size and datatype of column Or we can add primary key.\par
ALTER TABLE table_name MODIFY (fieldname datatype()...);\par
\b c)change\b0 - change the name of column.\par
alter table tablename change old_Col_name  new_Col_name name datatype();\par
\b d)rename\b0 - used to change the name of table.\par
RENAME table table_name to new table_name\par
\b e)drop column\b0\par
ALTER TABLE "table_name" DROP "column_name";\par
\b iii)drop table\par
\b0 DROP Table name;\par
\b iv)truncate- \b0 The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.\par
TRUNCATE TABLE tablename;\par
\par
\b\fs28 2)Data Manipulation Language (DML)\b0\fs22\par
DML is used to manipulate the existing data in the database objects (insert, select, update, delete).\par
\b i)insert\b0\par
INSERT INTO Table_Name VALUES();\par
\b ii)select-  \b0 select query is used to fetch the data from tables.\par
a)SELECT * FROM tablename;\par
 b)select distinct fieldname from tablename;\par
c)SELECT * FROM tablename where sal between 30000 and 50000;  // inclusive\par
d)SELECT * FROM tablename where sal>30000 and sal<50000;  // exclusive\par
\b iii)update- \b0 Update command is used to update any value from any table.\par
UPDATE table name set new_data where condition;\b\par
iv)delete- \b0 Delete query is used to delete a row from a table.\par
DELETE FROM tablename where condition;\par
\b\fs28 3)Clause\b0\fs22\par
\b i)Order by\b0\par
The ORDER BY clause sorts the result-set in ascending or descending order.\par
SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1, column2... ASC|DESC;  \par
\b ii)Group by\par
\b0 SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.\par
Grouping functions-sum(),avg(),max(),min(),count(),count(*).\par
SELECT column FROM table_name WHERE conditions GROUP BY column ORDER BY column.\par
select deptno,sum(sal) from emp ;\par
select deptno,sum(sal) from emp group by deptno;\par
select job,sum(sal) from emp group by job;\par
select max(sal)-min(sal) from emp;\par
select deptno,count(*) from emp group by deptno;\par
\b iii)Having \par
\b0 SELECT column1, column2 FROM table_name WHERE conditions GROUP BY column1, column2  HAVING conditions ORDER BY column1, column2; \par
select deptno,sum(sal) from emp group by deptno having sum(sal)<200000; \par
select deptno,sum(sal) from emp where sal>50000 group by deptno having sum(sal)>200000;\par
\par
\b\fs28 4)Sub query\b0\fs22\par
select * from stud1 where ccode=(select ccode from college where cname='MMCOE');\par
\b in\b0\par
select * from emp where job in (select job from emp where deptno=10);\par
\b not in\b0\par
select * from emp where deptno=20 and job not in(select job from emp where deptno=10);\par
\b any\b0\par
 select * from emp where deptno=10 and sal>any(select sal from emp where deptno=20);\par
\b all\b0\par
 select * from emp where deptno=10 and sal>all(select sal from emp where deptno=20);\par
\b\fs28\par
4)Joining\b0\fs22\par
\b equi join\b0\par
select cname,cadd,rollno,name,branch,address,mks from college c, stud1 s where c.ccode=s.ccode;\b\par
non equi join- It contains operator other then '='\par
\b0 select cname,cadd,rollno,name,branch,address,mks from college c,stud1 s where c.ccode<s.ccode;\par
\b self join, inner join- one table connect to itself\par
\b0 select c1.cname,c1.cadd,c2.cname,c2.cadd from college c1,college c2 where c1.ccode=c2.ccode;\par
\b Outer join- left outer join, right outer join, full outer join(does not support to mysql)\par
\b0 select cname,cadd,rollno,name,branch,address,mks from college c \ul left join \ulnone stud1 s \ul on\ulnone (c.ccode=s.ccode);\par
select cname,cadd,rollno,name,branch,address,mks from college c \ul right join \ulnone stud1 s \ul on\ulnone (c.ccode=s.ccode);\par
\b\par
\fs40 *Extra queries\par
\fs28 1)How to add not null constraint to existing column in MySQL\par
\b0\fs22 ALTER TABLE table_name MODIFY  fieldname datatype() not null;\par
\b\fs28 2)Operator\par
\fs22 a)Alies- used to change name of column temporary\b0\par
select old_name new_name from table;\par
\b b)Like- It is used for pattern matching\par
_ : only one character\par
% : sequence of character\par
Q) display data of those employees whose name start with S\par
\b0 select * from emp where ename like 'S%';\par
\b Q) display data of all emp having second latter as "a" in their name.\par
\b0 select * from emp where ename like '_a%';\par
\b Q) display data of those employees whose name end with i\par
\b0 select * from emp where ename like '%i';\par
\b c)IS NULL- used to check the record where value is not null.\b0\par
select * from emp where ename is null;\par
\b d)IN and NOT IN: used to display such record from table such that the values provided in the list matches with the record present in the table.\b0\par
select * from emp where (job) in ('Sales');\par
select * from emp where (job) not in ('Sales');\par
\par
}