Most important SQL Concept under MYSQL| Oracle Database for developers | Every Developer must be read this article.

Most important SQL Query under MYSQL Database for developers:-

If you are directly read this article then first read the following article for database knowledge, you can learn database fundamentals after that you can learn SQL easily.

Article for database fundamentals:-

https://www.shivatutorials.com/2020/08/database-tutorials-sql-tutorials-mysql.html

Article for MYSQL Fundamentals:-

https://www.shivatutorials.com/2020/08/mysql-for-developers.html

Now many GUI tools have provided by many database software such as MYSQL ( MYSQL YOG, PHPMYADMIN) Oracle ( SQL Developer), and MS-SQL(Management Studio).

But if you create an application then you should knowledge of basic SQL Command and SQL Query.

SQL Command has been divided into four different languages.

1) DDL(Data definition language):-

It is used to create a database structure and table structure to store records, in the case of Oracle database structure will be managed by user's and oracle server provide default tablespace to manage records.

DDL uses the following command to perform operations:-

1)   Create:-

It is used to create tables and multiple database objects,  create an index, create views, create procedures, create functions, create a synonym, create sequences.

Syntax to create a table:-

create table name(column name datatype primary key,...)

create table customer(customerid int, customername varchar2(50), email varchar2(50))

2)  Alter:-

This command is used to modify the structure of the tables, we can modify data type, add a new column, drop column, resize a column, add primary key, drop primary key, add foreign key, drop the foreign key.

Add new column

alter table tablename add column name datatype.

Drop particular column

alter table table name drop column column-name

Rename column name

alter table tablename rename column column name to newcolumname

Change Datatype

alter table tablename modify columnname datatype(10)

Add Constraint

Alter table tablename add constraint constraintname primary key(columnname)

Drop Constraint

Alter table name drop constraint constraint name.

3)  Drop:-

It is used to destroy all columns from a table,  it is also used to destroy other database objects from the oracle server.

drop table tablename

4)  Truncate:-  It is used to delete all records that can not be a rollback, Truncate will reset the identity column (autoincrement column)

Truncate table tablename.

Alter Command:-

This command is used to modify the structure of the table, for example, if we want to add a new column, drop a column, rename a column, change the data type, add a primary key, add a foreign key then we can use the alter command.

to change column name or column size or column datatype.

alter table tablename change oldcolumnname newcolumnname datatype.

alter table jobseeker change exp experience int

to add the new column name 

alter table tablename add column columnname datatype 

alter table jobseeker add column exp int 

to drop the new column 

alter table tablename drop column columnname

alter table jobseeker drop COLUMN experience

Drop Command:-

It is used to destroy tables and databases.

drop table tablename

drop table jobseeker.

Truncate Command:-

It is used to remove all records from the table permanently without any rollback.

Truncate table tablename.

2)  DML (Data manipulation language):-

It is used to perform operations on the table record. mostly in the application, we use DML operation because DDL operation is mostly managed by the database server team.

1)  Insert:-    

It is used to add a new record in a table based on table structure:-

insert into tablename(col1,col2,col3) values(value1,value2,value3);

insert into jobseek

INSERT INTO  jobseeker (jobseekerid, fullname, mobileno, email, qualification, technology, password) VALUES ('1', 's', '12', 'ss', 's', 's', '12');

2) Update:-

It is used to change the record from the table.

update tablename set columnname=value,columnname1=value where primarykeycolumnname=value.

The query for updating:-

update jobseeker set email='abc@gmail.com' , name='xyz' where jobseekerid=1

3) Delete:-

It is used to delete the row from the table.

delete from table-name where columnname=value;

DELETE from jobseeker where email='t'

we can delete rows one by one using delete but truncate will remove all records simultaneously.

4)  Select:- 

It is used to select a row from tables using multiple conditions.

we can perform multiple operations using select.  select use wildcard char * to select all columns.

The syntax for select:-

Select * from tablename;

Select * from tablename where columnname=value.

SELECT * FROM `jobseeker` WHERE jobseekerid=1

3)  DCL:- Data Control Language, it is used for DBA operation

Grant:-  to provide permission to user's for DDL

grant create  table to the user

Revoke:- It is used to return permission from user;s

revoke delete table to users

Deny:-  It is used to refuse permission to users;

deny username;

4)  TCL:-  It means Transaction Control Language, it is used to implement transactions under the DBA part.

This is used to commit the record permanently under the database table after the insert, update and delete command, we can not implement the Rollback command under DDL.

Rollback:-  It is used to undo database operation during a transaction after insert, update, and delete.

Syntax:-

rollback;

rollback to savepoint1;   // if savepoint mention

Commit:-  It is used to permanently save the query. after commit, rollback is not possible.

commit;

SavePoint:-  It is used to provide specific points to rollback.

savepoint savepointname;

Operator in Oracle:-

It is used to perform operations under select commands.

1)  Arithmetic operator:-

+                select 100+20 from dual

-                select 100-200 from dual

*               Select 100*2 from dual

/                select 100/2 from dual

mod()       select mod(10,3) from dual

2)  Conditional Operator:-

<       

>

<=

>=

!=   or <>

WAQ to display annual salary of employee?

Ans : select 12*sal, empname from emp

3)  Logical Operator:-

  1)   and:-   it will return true when all conditions will be true

 2)   or :-   it will return true when a particular condition will be true

WAQ to display the record of employees whose empid is 1001 and 1003?

    

select * from emp where empid=1001 or empid=1004

WAQ to display the record of employee whose deptid is 10,20 and salary is above 40000?

select * from emp where (deptid=10 or deptid=20) and sal>=40000;

WAQ to display the record of an employee, whose salary is in the range of five digits?

select * from emp where sal>9999 and sal<100000

4)  In and NOT In operator:-

It is used to select a particular record from tables.

select * from tablename where columnname in(value1,value2)

WAQ to display the record of employees whose deptid is 10,30,20 and 60.

select * from emp where deptid in (10,20,30,60)

NOT IN  is used to except the record of the matching condition.

5) Like and Not Like operator:-

It is used to display records based on pattern matching similar to a regular expression.

WAQ to display the record of an employee whose names start from x?

select * from emp where empname like 'x%'?    

wildcard char is used under like operator, %,_

NOT like is opposite of like operator


6)  Between and Not between:-

It is used to display the record based on the range?

WAQ to display the record of an employee, whose salary is in the range of five digits?

select * from emp where salary between 9999 and 100000;

7)  Any and All:-

Any will work similar to or operator means if only one condition will true then it returns true if all condition will be false then it returns false.

select  *  from emp where salary any < (25000,50000,75000);

select  *  from emp where salary all  >  (25000,50000,75000);

8)  Exist

Set operators:-   

It is used to combine the rows from the tables.

1)  Union:-  It is used to combine the rows but remove duplicate record

2)  Union All:-  It is used to combinate the rows and contain duplicate elements also.

3)  Intersect:-   It is used to show common elements in different tables rows.

4)  Minus:-   It is used to remove the common elements from the first table row from the second table row and display the remaining record from the first table.

select * from table1 [setoperator] select * from table2;

select col1,col2 from table1 [setoperator] select col1,col2 from table2;

Join Concept:-

................................................................................

Join is used to implement de-normalization between tables because normalization is used to divide the table column into multiple sub-tables but join is used to combine these tables columns logically.

Join is used to merge the column based on conditions and without conditon also.

Type of Join

1)  Cross Join:-  

It will cross multiply the number of rows from tables, all types of join operations first implement cross join.

Syntax of Cross Join

1) select * from table1 cross join table2;

2)  select * from table1,table2;

2)  Inner Join

3)  Outer Join  :- 

3.1)  Left Outer Join

3.2) Right Outer Join

3.3)  Full Outer Join

4)  Self Join

5)  Natural Join

Clauses in Oracle:-

It is used to apply the condition and provide a filter to particular rows.

Oracle Support following Clauses.

1)  Where:-  It is applied condition initially.

2)  Group By:-  It is used to subdivide the tables based on the table column.  group by clause always apply to related data-based column. The primary key and unique key column will not be applicable for a group by column.

We can select only grouped columns and aggregate functions under group by.

select groupbycolumnname,agreegatefunction from tablename group by columnname;

select groupbycolumnname,agreegatefunction from tablename where columnname=value group by columnname;

3)  Having:-=   It is used to apply condition after group by, having is used to filter the record based on aggregate function. having clause will be working with group by clause only.

select groupbycolumnname,agreegatefunction from tablename group by columnname  having agreegatefunction;

select groupbycolumnname,agreegatefunction from tablename where columnname=value group by columnname  having agreegatefunction;

4)  Order By:-  It is used to display the record into ascending and descending order.it will always be applied at the last stage of the query.

select groupbycolumnname,agreegatefunction from tablename where columnname=value group by columnname having agreegatefunction order by columnname.

Predefine function in oracle:-

1) String function:-

Subqueries:-

using this we can write more than one query using a nested pattern, it contains a collection of outer query block and inner query block.

OuterQuery=(InnerQuery)

WAQ to find the record of an employee, who is getting a max salary?

Select * from emp where sal=(select max(sal) from emp);

WAQ to find the second highest salary of employees?

select max(sal) from emp where sal!=(select max(sal) from emp)

Create Command:-  It is used to create a database, table, and other database objects such as views, triggers, uIn the MYSQL database, we will use the create command mostly to create a database and table.

Syntax to create the database:-

create database databasename;

Syntax to create a table:-

create table tablename(columname datatype(length) primary key, columnname datatype(length));

for example, we want to create a database for the job portal then we will write.

create database jobportal.

for example, if we want to create a table for job seekers then we will create the following SQL queries.

create table jobseeker(jobseeker varchar(50)  primary key, fullname varchar(50), mobileno char(10),email varchar(50), qualification varchar(50), technologty varchar(100),password varchar(10));

How to run these SQL Query under XAMPP:-

XAMPP provides two different options to run a SQL Query.

open PHPMyAdmin and open SQL options then write a query, it provides textarea where we can write SQL query options.

Step1st:-

Step2nd:-

Click on SQL options

Step3rd:-

Step4th:-

Write Query for database and table:-

1) Create database command:-

create database jobportal.

2) select database command

      use databasename

3) Create a table command

create table jobseeker(jobseeker varchar(50)  primary key, fullname varchar(50), mobileno char(10),email varchar(50), qualification varchar(50), technologty varchar(100),password varchar(10));

XAMPP also provides MYSQL Console to write SQL Query on Console Window.

1)  open command prompt and navigate c:/xampp/MySQL/bin

you can manage all commands here

to run table query first select database:-

LIVE Interview Session of Software Engineer by Kangaroo Software PVT LTD.