Scribd Logo
Upload

Welcome to Scribd!

  • 14 views

    Drop Database Create Database USE

    Uploaded by

    umersabah
    The document contains SQL commands to create databases and tables for a complaint management system. It creates tables to store complainant details, complaint details, employee details. It inserts sample data and runs various queries to select, update and alter data in the tables.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Drop Database Create Database USE

    Uploaded by

    umersabah
    14 views4 pages
    The document contains SQL commands to create databases and tables for a complaint management system. It creates tables to store complainant details, complaint details, employee details. It inserts sample data and runs various queries to select, update and alter data in the tables.

    Original Title

    Comands.docx

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains SQL commands to create databases and tables for a complaint management system. It creates tables to store complainant details, complaint details, employee details. It inserts sample data and runs various queries to select, update and alter data in the tables.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    14 views4 pages

    Drop Database Create Database USE

    Uploaded by

    umersabah
    The document contains SQL commands to create databases and tables for a complaint management system. It creates tables to store complainant details, complaint details, employee details. It inserts sample data and runs various queries to select, update and alter data in the tables.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 4

    DROP DATABASE cmcc;

    CREATE DATABASE cmcc;

    USE cmcc;

    CREATE TABLE `cmcc`.`particular of complainant` (

    `Complaint Date` DATETIME NOT NULL,

    `Mode` CHAR(45) NOT NULL,

    `Name` CHAR(45) NOT NULL,

    `District` CHAR(45) NOT NULL,

    `Mobile` INT(20) NULL,

    `Address` VARCHAR(100) NULL,

    PRIMARY KEY (`Complaint Date`));

    CREATE TABLE `cmcc`.`particular of complain` (

    `District` CHAR(45) NOT NULL,

    `Department` CHAR(100) NOT NULL,

    `Subject` CHAR(100) NOT NULL,

    `Email` VARCHAR(50) NULL,

    PRIMARY KEY (`Subject`));

    CREATE TABLE `cmcc`.`sent reply` (`Priority`

    CHAR(20) NULL) AS

    SELECT `particular of complain`.`District`,

    `particular of complain`.`Department`,

    `particular of complain`.`Subject`,

    `particular of complain`.`Email`

    FROM `cmcc`.`particular of complain`;


    CREATE TABLE `cmcc`.`sent reply` (`Priority` CHAR(20) NOT NULL) AS SELECT `particular of
    complain`.`District`, `particular of complain`.`Department`, `particular of complain`.`Subject`,
    `particular of complain`.`Email` FROM `cmcc`.`particular of complain` Error Code: 1364. Field 'Priority'
    doesn't have a default value 0.453 sec

    ALTER TABLE `cmcc`.`sent reply`

    DROP COLUMN `Email`;

    use `cmcc`;

    describe `particular of complain`;

    INSERT INTO `cmcc`.`particular of complain`

    (`District`, `Department`, `Subject`, `Email`)

    VALUES

    ('Lahore', 'Police', 'Redressal of Grievances', '[email protected]'),

    ('Gujranwala', 'I&C', 'Inquiry', '[email protected]'),

    ('Sheikhupura', 'Finance', 'Financial Assistance', '[email protected]');

    SELECT * FROM `particular of complain`;

    SELECT `District`, `Department` FROM `particular of complain`;

    INSERT INTO `cmcc`.`employee`

    (`id`, `name`, `age`, `department`, `city`, `salary`)

    VALUES

    (100, 'Umer', 35, 'Finance' , 'Lahore',60000),

    (101, 'Qasim', 25, 'Audit' , 'Lahore',40000),

    (102, 'Asim', 31, 'Finance' , 'Gujranwala',25000),

    (103, 'Shoaib', 28, 'Marketing' , 'Lahore',60000),

    (104, 'Umer', 33, 'Marketing' , 'Gujranwala',28000),


    (105, 'Atif', 23, 'Audit' , 'Faisalabad',40000),

    (106, 'Majid', 38, 'Sales' , 'Lahore',23450),

    (107, 'Peter', 39, 'Cleanol' , 'Gujranwala',20000),

    (108, 'Majid', 32, 'IT' , 'Lahore',50000),

    (109, 'Umer', 24, 'IT' , 'Faisalabad',23400),

    (110, 'Ahmed', 33, 'Finance' , 'Gujranwala',45000),

    (111, 'Qasim', 23, 'Marketing' , 'Faisalabad',35000),

    (112, 'Yasir', 34, 'Admin' , 'Lahore',60000),

    (113, 'Umer', 35, 'Finance' , 'Lahore',60000),

    (114, 'Umer', 35, 'Finance' , 'Lahore',60000);

    SELECT * FROM employee

    WHERE age>33;

    SELECT age FROM employee

    WHERE age>33;

    SELECT `name`, `department` FROM employee

    WHERE name ='umer';

    SELECT * FROM employee

    WHERE salary<=28000 AND name='umer';

    SELECT * FROM employee

    WHERE salary<=28000 OR name='umer';

    SELECT `id`, `name`,`salary` FROM employee

    WHERE salary<=28000 OR name='umer';


    SELECT * FROM employee

    WHERE salary IS NULL;

    SELECT * FROM employee

    WHERE name LIKE 'q%';

    SELECT * FROM employee

    WHERE name LIKE 'q%' OR salary IS NULL;

    ALTER TABLE `cmcc`.`employee`

    CHANGE COLUMN `salary` `salary` DOUBLE NULL ;

    INSERT INTO `cmcc`.`employee` (`id`, `name`, `age`, `department`, `city`, `salary`)

    VALUES (116, 'Akhtar', 28, 'Managment', 'Lahore', NULL);

    SELECT * FROM employee

    WHERE age IN (28,32,38);

    UPDATE `cmcc`.`employee` SET `age`='28', `department`='Admin', `city`='Gujranwala', `salary`='23000'


    WHERE `id`='113';

    UPDATE `cmcc`.`employee` SET `name`='Masood', `age`='28', `department`='IT', `city`='GUjranwala',


    `salary`='50000' WHERE `id`='114';

    You might also like