SQL Revision XI

è -->CREATE DATABASE School;

è -->USE School;

è -->CREATE TABLE Learner

      ( RollNo INTEGER,

        Name VARCHAR(25));

CREATE TABLE teacher

      ( ID INTEGER,

        Name VARCHAR(25)

        Subject Varchar(20).

        Address Varchar(30)

);

è -->SHOW TABLES;

+------------------+

| Tables_in_school |

+------------------+

 Learner

Teacher

  

è --->   INSERT INTO Learner VALUES (14,'Aruna Asaf Ali');

INSERT INTO Learner VALUES (12,'Tarun Sinha'); 

(Character, date and Time data should be enclosed in Quotes.Numeric values should not be    

enclosed in quotes.)

 Inputting only selective values in a database:

 INSERT INTO Teacher (ID,Name,Address) Values( 101,'Amita','B-2,Safdarjung Enclave');

----------------------------------------------------------------------------------------------------

è SELECT * FROM Learner;

+---------------------------+

|RollNo | Name |

+---------------------------+

| 14 | Aruna Asaf Ali |

| 12 | Tarun Sinha |

è Any time to know the database currently in use, the SELECT DATABASE() statement can be used.

SELECT DATABASE();

school

è DESCRIBE <table name>;OR  DESC <table name>;

è SELECT Rollno FROM Student;

Rollno |

+---------- +

| 1 |

| 2 |

| 3 |

è SELECT * FROM Student;

+--------+------------------+--------+--------+

| Rollno | Name | Gender | Marks1 |

+--------+------------------+--------+--------+

| 1 | Siddharth Sehgal | M | 93.0 |

| 2 | Gurpreet Kaur | F | 91.0 |

è To eliminate duplicates:

SELECT DISTINCT Marks1 FROM Student;

è SELECT Marks1+5 FROM Student;

+----------+

| Marks1+5 |

+----------+

| 98.0 |

| 96.0 |

è Different outputs:

SELECT Name,Marks1+0.05*Marks1 FROM Student ;

SELECT Name,Marks1-10 FROM Student ;

SELECT Name,Marks1/2 FROM Student ;

(Using these operators on tables does not create new columns in the tables or change the

actual data values. The results of the calculations appear only in the output.)

è Column alias:

è SELECT Marks1 AS "Marks Secured" FROM Student;

+---------------+

| Marks Secured |

+---------------+

| 93.0 |

| 91.0 |

è Putting text in Query output

mysql> SELECT Rollno,Name,'has secured marks',marks1 FROM

student;

+--------+------------------+-------------------+--------+

| Rollno | Name | has secured marks | marks1 |

+--------+------------------+-------------------+--------+

| 1 | Siddharth Sehgal | has secured marks | 93.0 |

| 2 | Gurpreet Kaur | has secured marks | 91.0 |

è SELECT Name,Marks1 FROM Student WHERE Marks1 > 80;

+------------------+----------+

| Name | Marks1 |

+------------------+----------+

| Siddharth Sehgal | 93.0 |

| Gurpreet Kaur | 91.0 |

è  Relational Operator What it does

è = Equal to

è > Greater than

è < Less than

è >= Greater than or equal to

è <= Less than or equal to

è != or <> Not equal to

è SELECT Name,Marks1 FROM Student WHERE Marks1 <60;

è mysql> SELECT * FROM Student WHERE Name = 'Gurpreet Kaur';

è mysql> SELECT RollNo,Marks1 FROM Student WHERE Rollno <=3;

è mysql> SELECT RollNo,Marks1 FROM Student WHERE Rollno <>3;

è mysql> SELECT RollNo,Marks1 FROM Student WHERE Name <>'Mani Kumar’;

è Logical Operators

è OR, AND, NOT logical operators are used in SQL. Logical operators OR and AND are

è used to connect relational expressions in the WHERE clause. If any of the comparisons

è are true, OR returns TRUE. AND requires both conditions to be true in order to return

è TRUE. NOT negates a condition. If a condition returns a True value, adding NOT causes

è the condition to return a False value and vice versa.

è The symbol || can be used in place of OR, && can be used in place of AND, ! can be used

è in place of NOT operator.

è SELECT Rollno, Name,Marks1 FROM Student WHERE Marks1 >

è 70 AND Marks1 < 80;

è SELECT Empnumber, EmpName FROM Employee WHERE Department = 'Accoumts' OR Department = 'Personnel';

è SELECT Empnumber, EmpName FROM Employee WHERE Department = 'Accoumts' AND Designation = 'Manager';

è SELECT Empnumber, EmpName FROM Employee WHERE NOT(Designation = 'Manager');

è SELECT Name,TotalMarks FROM Candidate WHERE writtenmarks>80 || Interviewmarks>10;

è SELECT Name,TotalMarks FROM Candidate WHERE writtenmarks>80 && Interviewmarks>10;

è SELECT * FROM Emp

WHERE first_name='Amit' AND (last_name='Sharma' OR

last_name='Verma');

è BETWEEN:

SELECT Rollno,Name,Marks1 FROM Student WHERE Marks1 BETWEEN 70 AND 80;

SELECT Rollno,Name,Marks1 FROM Student WHERE Marks1 NOT BETWEEN 70 AND 80;

List:  -> SELECT Rollno, Name, Marks1 FROM Student WHERE Marks1 IN (68,76,78);

è SELECT * FROM Employee WHERE State NOT IN ('DELHI','MUMBAI','UP');

Condition based on pattern matches

MySQL has wildcards to help you out. % and_ are two wild card characters. The percent (%) symbol is used to represent any sequence of zero or more characters. The underscore (_) symbol is used to represent a single

character. LIKE clause is used to fetch data which matches the specified pattern from a table:

For example, Ms. Sujata wants to display details of students who have their names ending with 'Sen', she enters:

SELECT * FROM Student WHERE Name LIKE '%Sen';

To display rows from the table Student with names starting with 'G', she enters:

mysql> SELECT * FROM Student WHERE Name LIKE 'G%';

To display rows that have names starting with 'G' and ending with 'b', she enters:

mysql> SELECT * FROM Student WHERE Name LIKE 'G%b';

To display rows from the table Student that have 'Sen' anywhere in their names, she enters:

mysql> SELECT * FROM Student WHERE Name LIKE '%Sen%';

To display rows that have names starting with 'A' and then having any 4 characters and ending with 'Ali', she uses underscore wild card like this:

mysql> SELECT * FROM Student WHERE Name LIKE 'A_ _ _ _ Ali';

The keyword NOT LIKE is used to select the rows that do not match the specifiedpattern.

To display rows from the table Student that have names not starting with 'G', she enters:

mysql> SELECT * FROM Student WHERE Name NOT LIKE 'G%';

NULL:

Sometimes, you don't know the represent value for a column. In a table, you can store these unknowns as NULL. NULL means a value that is unavailable, unassigned, unknown or inapplicable. NULL is not the same as zero or a space or any other character. . In a table NULL is searched for using IS NULL keywords.

SELECT * FROM Student WHERE Name IS NULL;

SELECT * FROM Employee WHERE Commission IS NULL;

NOT NULL values in a table can be searched using IS NOT NULL.

SELECT * FROM Employee WHERE Commission IS NOT NULL;

!If any column value involved in an arithmetic expression is NULL, the result of the arithmetic expression is also NULL.

Sorting the Results- ORDER BY

Ms. Sujata wants to display data of students in ascending order of their marks, she

enters the following statement:

mysql> SELECT * FROM Student ORDER BY Marks1;

Ms. Sujata uses the following statement to display details of her students in descending

order of marks.

mysql> SELECT * FROM Student ORDER BY Marks1 DESC;

Ms. Sujata wants to display all the rows of the table Student in ascending order of Marks1. But if several students have the same value for Marks1, for them she wants the display to be in ascending order of names.

She can order results on more than one column like this:

mysql> SELECT * FROM Student ORDER BY Marks1,Name;

The following statement displays rows in descending order of marks but if severalstudents have the same value for marks, for them the display is in ascending order of names.

mysql> SELECT * FROM Student ORDER BY Marks1 DESC,Name;

Ms. Sujata wants to insert a row for Student with roll number 14 who secured 45 marks.She however does not have that student's name. The following INSERT INTO statementinserts values for specific columns namely Rollno and Marks1. Those columns that arenot specified in the list will have the default values (if defined) else NULLs will beinserted.

mysql> INSERT INTO Student(Rollno,Marks1) VALUES (14,45);

We can also explicitly add NULL value by using the NULL keyword in the VALUES list for those columns that can hold null values.

mysql> INSERT INTO Student Values(14,'Siddharth Sehgal','M',NULL);

Inserting dates:

INSERT INTO my_table (idate) VALUES (19970505);

mysql> INSERT INTO my_table (idate) VALUES ('97-05-05');

mysql> INSERT INTO my_table (idate) VALUES ('1997.05.05');

mysql> INSERT INTO my_table (idate) VALUES ('0000-00-00');

UPDATE: The following statement sets the marks(Mark1) of all the rows to 94.

è UPDATE Student SET Marks1 = 94;

The following statement sets the marks(Mark1) of the row with name as 'Monica Rana' to

94.

 -> UPDATE Student SET Marks1 = 94 WHERE name = 'Monica Rana';

DELETE: ->DELETE FROM Student WHERE Rollno = 14;

è To delete complete data:

 DELETE from Student;