SQL Statements (12)

 

1.       Select CustomerName, city from customers;

Selects Customer name and city field from customers table.

2.       select Distinct country from customers;

Selects only different country from customer table.

3.       select * From customers where country = 'Nepal';

Selects all the records from customers table whose country is Nepal.

4.       Select * From customers where country = 'Nepal' and City = 'Butwal';

Selects all the records from customer whose country is Nepal and city is Butwal.

5.       Select * From customers where country = 'Nepal' or Country = 'India';

Selects all the records from customers whose country is either Nepal or India.

6.       Select * From customers where country In ('Nepal', 'India', 'China');

Selects all the records from customers table whose address either Nepal or India or China.

7.       Select * From product where price between 2000 and 1000;

Selects all the records whose price is between 2000 and 1000 from product table.

8.       Select * From customers where CustomerName like 'a%';

Selects all the records from customer table whose customername starts with a.

9.       Select * From customers where CustomerName like '_a%';

Selects all the records whose name has second character a.

10.   Select * From customer Order By country DESC;

Selects all the records with country descending order.

11.   Select * From customer Order By country ASC;

Selects all the records with country ascending order.

12.   Select * From customer Order By country;

Selects all the records with country ascending order.

13.   Select Count(CustomerId), country from customers Group By Country;

 statement lists the number of customers in each country

14.   Select Count(ProductID)  as Total from Product;

 Return the number of products in the Product table:

15.   Select Count(CustomerID), Country From Customers Group By Country Having Count(CustomerID)>5;

lists the number of customers in each country. Only include countries with more than 5 customers

16.   Create Table Person(PersonID int, name varchar(50), address varchar(200), primary key (PersonID));

Creates a table Person with the field PersonID, name and address with primary key in PersonID.

17.   Drop Table person;

Drops(Deletes) the table from database.

18.   Create index idx_lastname on person(LastName);

Creates index idx_lastname in LastName of person table 

19.   Alter Table person drop index idx_lastname;

Deletes index idx_lastname in LastName of person table.

20.   Truncate table categories;

Command  deletes the data inside a table, but not the table itself.

21.   Alter table customer add email varchar(250);

Adds an 'Email' column to the 'Customers' table.

22.   Alter table customer drop column email;

Deletes the 'Email' column from the 'Customer' table.

23.   Alter table customer rename column email to e_mail;

Renames the column email to e_mail.

24.   Alter table customer alter column email varchar(200);

To change the data types of a column in a table. (MySQL/ Oracle)

25.   Alter table customer modify column email varchar(210);

To change the data types of column in a table. (SQL Server/ MS-Access)

26.   Alter table customer rename to client;

To change the name of customer into client.

27.   Insert into customer(name, address) values ('Ram', 'Butwal');

Statement is used to insert new records in name and address of customer table.

28.   Update customers set name-='Shyam', city='Butwal' where customerID=1;

Statement is used to modify the existing records in a customer table where customerID has 1.

29.   Delete from customer where customerName = 'shyam';

Used to delete existing records in a table.

30.   Create database school;

Creates database School.

31.   Use school;

Selects database school.

32.   Drop Database school;

Deletes database school.

33.   Rollback;

Used for reverting changes performed

34.   Commit;

used  to save data manipulation.

35.   Grant;

    To grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called employees to a user name smithj

    GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO smithj;   (SQL Server)

    GRANT SELECT, INSERT, UPDATE, DELETE ON contacts TO 'smithj'@'localhost';     (MySQL)

36.   Invoke;

To revoke DELETE privileges on a table called employees from a user named anderson.

        REVOKE DELETE ON employees FROM anderson;         (SQL Server)

        REVOKE privileges ON object FROM user;        (MySQL)