SQL/MYSQL/ORACLE Query

Blogs, Public Blogs February 23, 2018July 23, 2019MYSQL, ORACLE, SQL QUERY 1 Comment

CREATE DATABASE IF NOT EXISTS database;
DROP DATABASE
delhi_buss_record_system,metro_management_system,school_management_system;
(CREATE TABLE)
CREATE TABLE NORTH_BUSESS_DRIVER_DETAILS(Driver_id INT(11) PRIMARY KEY,DRIVER_NAME VARCHAR(255),PHONE_NUMBER VARCHAR(255),ADDRESS VARCHAR(255),JOINING_DATE DATETIME(6));
CREATE TABLE WORKER(WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME CHAR(25),LAST_NAME CHAR(25),SALARY INT(20),JOINING_DATE DATETIME,
DEPARTMENT CHAR(25));
CREATE TABLE NORTH_BUSESS_DRIVER_DETAILS(Driver_id INT(11) PRIMARY KEY,DRIVER_NAME VARCHAR(255),PHONE_NUMBER VARCHAR(255),ADDRESS VARCHAR(255),JOINING_DATE DATETIME(6));
(ALTER ADD Column)
ALTER TABLE north_busess_driver_details ADD COLUMN shift VARCHAR(255);
(ALTER MODIFY)
ALTER TABLE north_busess_driver_details MODIFY COLUMN shift INT(255);
ALTER TABLE north_busess_driver_details MODIFY COLUMN JOINING_DATE DATE;
(RENAME CHANGE ALTER)(ALTER TABLE CHANGE COLUMN NAME)
ALTER TABLE north_busess_driver_details CHANGE COLUMN DRIVER_NAME D_NAME VARCHAR(255);
(ALTER DROP COLUMN)
ALTER TABLE north_busess_driver_details DROP COLUMN shift;
(ALTER ADD MULTIPLE COLUMN)
ALTER TABLE north_busess_driver_details ADD COLUMN Shift VARCHAR(255),ADD COLUMN Timing DATETIME(6);
ALTER TABLE north_busess_driver_details ADD COLUMN F_NAME VARCHAR(255),EDUCATION VARCHAR(255);

ALTER TABLE north_busess_driver_details DROP COLUMN Timing,DROP COLUMN LATE;
(INSERT QUERY)
INSERT INTO north_busess_driver_details(Driver_id,D_NAME,PHONE_NUMBER,ADDRESS,JOINING_DATE,Shift,Timing,F_NAME,EDUCATION,LATE)VALUES(‘022′,’OPI’,’909090′,’DEL’,’12/6/2016′,’EVE’,’09’,’IU’,’11TH’,’5 MINITES’);
INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
(001, ‘Monika’, ‘Arora’, 100000, ’14-02-20 09.00.00′, ‘HR’),
(002, ‘Niharika’, ‘Verma’, 80000, ’14-06-11 09.00.00′, ‘Admin’),
(003, ‘Vishal’, ‘Singhal’, 300000, ’14-02-20 09.00.00′, ‘HR’),
(004, ‘Amitabh’, ‘Singh’, 500000, ’14-02-20 09.00.00′, ‘Admin’),
(005, ‘Vivek’, ‘Bhati’, 500000, ’14-06-11 09.00.00′, ‘Admin’),
(006, ‘Vipul’, ‘Diwan’, 200000, ’14-06-11 09.00.00′, ‘Account’),
(007, ‘Satish’, ‘Kumar’, 75000, ’14-01-20 09.00.00′, ‘Account’),
(008, ‘Geetika’, ‘Chauhan’, 90000, ’14-04-11 09.00.00′, ‘Admin’);
(ALIAS (AS)
SELECT FIRST_NAME AS W_F FROM worker;
(Upper Case)
SELECT upper(FIRST_NAME) FROM worker;
(DISTINCT)
SELECT DISTINCT DEPARTMENT FROM worker;
(SUBSTRING )
SELECT substring(FIRST_NAME,1,3) FROM worker;
SELECT SUBSTRING(LAST_NAME,1,2) FROM worker;
SELECT SUBSTRING(LAST_NAME,1,4) FROM worker;
SELECT SUBSTRING(LAST_NAME,2,5) FROM worker;
(INSTR)
SELECT INSTR(FIRST_NAME,BINARY’A’) FROM worker WHERE FIRST_NAME = ‘amitabh’;
(SELECT QUERY FETCH ALL DATA)
SELECT * FROM north_busess_driver_details;
(WHERE CLAUSE)
SELECT * FROM north_busess_driver_details WHERE Driver_id =’1′;
SELECT * FROM north_busess_driver_details WHERE D_NAME=’ABC’;
(IT WILL FETCH THE DETAILS WHAT WE HAVE MENTION IN QUERY)
SELECT Driver_id,D_NAME,PHONE_NUMBER FROM north_busess_driver_details WHERE Shift=’Morning’;
(AND OPERATOR IN SELECCT QUERY)
SELECT * FROM north_busess_driver_details WHERE D_NAME=’OL’ AND PHONE_NUMBER=’099′;
(OR OPERATOR IN SELECT QUERY)
SELECT * FROM north_busess_driver_details WHERE Driver_id=”011″ OR ADDRESS=”DEL”;
SELECT F_NAME,PHONE_NUMBER FROM north_busess_driver_details WHERE ADDRESS=”DEL” AND D_NAME=”OP”;
SELECT F_NAME,PHONE_NUMBER FROM north_busess_driver_details WHERE ADDRESS=”DEL” OR D_NAME=”OP”;
(DISTINCT)
SELECT DISTINCT D_NAME FROM north_busess_driver_details;
(DISTINCT COUNT)
SELECT COUNT(DISTINCT Driver_id) FROM north_busess_driver_details;
(NOT)
SELECT * FROM north_busess_driver_details WHERE NOT ADDRESS=’DEL’;
SELECT * FROM north_busess_driver_details WHERE NOT ADDRESS=’DEL’ AND NOT ADDRESS=’BIH’;
(ORDER BY ASC / DESC)
SELECT * FROM north_busess_driver_details ORDER BY Driver_id ASC;
SELECT * FROM north_busess_driver_details ORDER BY Driver_id DESC;
SELECT * FROM north_busess_driver_details ORDER BY D_NAME DESC;
(ASC/DESC WHERE CLAUSE)
SELECT * FROM north_busess_driver_details WHERE Driver_id=”11″ ORDER BY D_NAME ASC;
(UPDATE COLUMN)
UPDATE north_busess_driver_details SET D_NAME=’BA’ WHERE Driver_id=’11’;
(UPDATE overall Table)
UPDATE north_busess_driver_details SET D_NAME=’BA’;
UPDATE north_busess_driver_details SET D_NAME=’AA’ AND ADDRESS=’DEL’ WHERE Driver_id=’002′;
DELETE FROM north_busess_driver_details WHERE Driver_id=’501′;
DELETE FROM north_busess_driver_details;
(LIMIT)
SELECT * FROM north_busess_driver_details LIMIT 25;
(LIMIT PERTICULAR)
SELECT Driver_id FROM north_busess_driver_details LIMIT 30;
SELECT * FROM north_busess_driver_details WHERE Driver_id=’11’ AND D_NAME=’D’ ORDER BY Shift LIMIT 1;
SELECT * FROM north_busess_driver_details WHERE Driver_id=’11’ OR D_NAME=’D’ ORDER BY Shift LIMIT 1;
(COUNT)
SELECT COUNT(Driver_id) FROM north_busess_driver_details;
SELECT COUNT(D_NAME) FROM north_busess_driver_details;
(AVG)
SELECT AVG(D_NAME) FROM north_busess_driver_details;
(SUM)
SELECT SUM(STATUS) FROM north_busess_driver_details;
(LIKE)
SELECT * FROM employee WHERE last_name LIKE ‘%a’
SELECT * FROM employee WHERE last_name LIKE ‘%S’
SELECT * FROM employee WHERE last_name LIKE ‘S%’
SELECT * FROM employee WHERE last_name LIKE ‘W%S’
SELECT * FROM employee WHERE first_name LIKE ‘%’
SELECT * FROM employee WHERE job_title LIKE ‘S%S’;

One thought on “SQL/MYSQL/ORACLE Query”

zorivare worilon
August 21, 2022 at 6:11 pm
Permalink

Wonderful blog! I found it while browsing on Yahoo News. Do you have any tips on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Thanks
Reply

One thought on “SQL/MYSQL/ORACLE Query”

Leave a Reply Cancel reply