SQL/MYSQL/ORACLE Query

  • 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

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *