MySQL in Short

NOTES:
  1. Remember to put a semicolon at the end of your SQL statements. The ; indicates that your SQL statment is complete and is ready to be interpreted.
  2. Strings must be in single quotes.
  3. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified.


   
ACTION
QUERY
Match any first names that start with 'Er'
SELECT  first, last, city
 FROM  empinfo
WHERE  first LIKE 'Er%';
Match any last names that end in a 's'
SELECT first, last
FROM empinfo
WHERE last LIKE '%s';
Select rows where the first name equals 'Eric' exactly
SELECT * 
FROM empinfo
WHERE first = 'Eric';
Select last name, city, age from table whose age is greater than 30.
SELECT last, city, age 
FROM empinfo
WHERE age > 30;
Select first name, last name, age whos elast name consists of “illia”.
SELECT first, last, age 
FROM empinfo
WHERE last LIKE '%illia%';
Display the first name, last name, and city for everyone that's not from Payson.
SELECT first, last, age 
FROM empinfo
WHERE city <> 'Payson';
This statement will return all of the unique ages in the employee_info table.

SELECT DISTINCT age
FROM employee_info;
This statement will return all of the ages in the employee_info table with duplicate data.

The ALL keyword is the default if nothing is specified.
SELECT ALL age
FROM employee_info;

OR

SELECT age FROM employee_info;
Using Aggregate functions with SELCT statement 
SELECT  AVG(salary)
FROM employee;

//return the average salary for all employee whose title is equal to 'Programmer
SELECT  AVG(salary)
FROM employee
WHERE title = 'Programmer’;

//return the number of rows in the employees table.

SELECT  COUNT(*)
FROM employee;

//gives maximum price for an item in table
SELECT  MAX(price)
FROM items_ordered;

SELECT  AVG(price)
FROM items_ordered
WHERE order_date LIKE '%Dec%’;

SELECT MIN(price) 
FROM items_ordered 
WHERE item = 'Tent';
GROUP BY clause
//display highest salary from each department
SELECT MAX(salary), dept
FROM employee
GROUP BY dept;

SELECT lastname, MAX(salary), dept
FROM employee
GROUP BY dept, lastname;

SELECT quantity, MAX(price)
FROM items_ordered
GROUP BY quantity;

SELECT state, COUNT(state)
FROM customers
GROUP BY state;

SELECT item, MAX(price), MIN(price)
FROM items_ordered
GROUP BY item;

//select the customerid, number of orders they made, and the sum of their orders.
SELECT customerid, COUNT(customerid), SUM(price)
FROM items_ordered
GROUP BY customerid;
HAVING clause
//ONLY calculate & display the average if their salary is over 20000
SELECT dept, AVG(salary)
FROM employee
GROUP BY dept
HAVING AVG(salary) > 20000;

SELECT state, COUNT(state)
FROM customers
GROUP BY state
HAVING count(state) > 1;

SELECT item, MAX(price), MIN(price)
FROM items_ordered
GROUP BY item
HAVING MAX(price) > 190.00;

SELECT customerid, COUNT(customerid), SUM(price)
FROM items_ordered
GROUP BY customerid
HAVING COUNT(customerid) > 1;
ORDER BY clause
SELECT employee_id, dept, name, age, salary
FROM employee_info
WHERE dept = 'Sales'
ORDER BY salary, age DESC;

SELECT lastname, firstname, city
FROM customers
ORDER BY lastname DESC;

SELECT item, price
FROM items_ordered
WHERE price > 10.00
ORDER BY price ASC;
Conditions & Boolean Operators
SELECT employeeid, firstname, lastname, title, salary
FROM employee_info
WHERE (salary >= 45000.00) AND (title = ‘Programmer’);

SELECT customerid, order_date, item
FROM items_ordered
WHERE (item <> 'Snow shoes') AND (item <> 'Ear muffs’);

SELECT item, price
FROM items_ordered
WHERE (item LIKE 'S%') OR (item LIKE 'P%') OR (item LIKE 'F%’);
IN & BETWEEN

NOTE: You can also use NOT BETWEEN to exclude the values between your range.

NOTE: You can also use NOT IN to exclude the rows in your list.
SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz’);

SELECT employeeid, lastname, salary
FROM employee_info
WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts'
OR lastname = 'Ruiz’;

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age BETWEEN 30 AND 40;

SELECT employeeid, age, lastname, salary
FROM employee_info
WHERE age >= 30 AND age <= 40;

SELECT order_date, item, price
FROM items_ordered
WHERE price BETWEEN 10.00 AND 80.00;

SELECT firstname, city, state
FROM customers
WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado', 'Hawaii');
Mathematical Functions

SELECT ROUND(salary), firstname
FROM employee_info;

SELECT item, SUM(price)/SUM(quantity)
FROM items_ordered
GROUP BY item;
ALIAS
SQL aliases are used to give a table, or a column in a table, a temporary name.

Note: It requires double quotation marks or square brackets if the alias name contains spaces:
//columns
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

//tables
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID = o.CustomerID;
JOINS
//This particular "Join" is known as an "Inner Join" or “Equijoin"

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info, purchases
WHERE customer_info.customer_number = purchases.customer_number;

SELECT customer_info.firstname, customer_info.lastname, purchases.item
FROM customer_info INNER JOIN purchases
ON customer_info.customer_number = purchases.customer_number;

SELECT customers.customerid, customers.firstname, customers.lastname,
items_ordered.order_date, items_ordered.item, items_ordered.price
FROM customers, items_ordered
WHERE customers.customerid = items_ordered.customerid;

SELECT customers.customerid, customers.firstname, customers.state, items_ordered.item
FROM customers, items_ordered
WHERE customers.customerid = items_ordered.customerid
ORDER BY customers.state DESC;

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM 
(
(Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

//left join
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers LEFT JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders RIGHT JOIN Employees 
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
UNION

It is used to combine the result-set of two or more SELECT statements.

NOTE: To allow duplicate values, use UNION ALL
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
Create table employee
CREATE TABLE employee
(
first VARCHAR(15),
last VARCHAR(20),
age NUMBER(3),
address VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20)
);
Insert single record in to ‘employee’ table.
INSERT INTO employee
(firstname, lastname,title, age, salary)
VALUES 
('Jonie', 'Weber','Secretary', 28,19500.00);
Update values inrespective tables
UPDATE phone_book
SET area_code = 623
WHERE prefix = 979;

UPDATE phone_book
SET last_name = 'Smith', prefix=555, suffix=9292
WHERE last_name = 'Jones’;

UPDATE employee
SET age = age+1
WHERE first_name='Mary’ AND last_name='Williams’;

UPDATE myemployees_ts0211
SET lastname='Weber-Williams'
WHERE firstname=‘Jonie' AND lastname= 'Weber’;

UPDATE myemployees_ts0211
SET age=age+1
WHERE firstname='Dirk' AND lastname='Smith’;

UPDATE myemployees_ts0211
SET title = 'Administrative Assistant'
WHERE title = 'Secretary’;

UPDATE myemployees_ts0211
SET salary = salary + 3500
WHERE salary < 30000;

UPDATE employees
SET salary = salary + 4500
WHERE salary > 33500;
Selete records from respective tables
DELETE FROM employee
WHERE lastname = 'May’;

DELETE FROM employee
WHERE firstname = 'Mike' OR firstname = 'Eric’;

DELETE FROM myemployees_ts0211
WHERE salary > 70000;
Delete entire table from databaseDROP TABLE myemployees_ts0211;
Alter Table
ALTER TABLE Persons
ADD DateOfBirth date;

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;

ALTER TABLE Persons
DROP COLUMN DateOfBirth;
Constraints

//NOT NULL
CREATE TABLE Persons
 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

//UNIQUE
CREATE TABLE Persons
 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE (ID)
);

//UNIQUE constraint on multiple columns
CREATE TABLE Persons
 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
Primary Key
CREATE TABLE Persons 
(
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

//PRIMARY KEY constraint on multiple columns
Here primary key is made up of TWO COLUMNS (ID + LastName)

CREATE TABLE Persons
 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

ALTER TABLE Persons
DROP PRIMARY KEY;
Foriegn Key
CREATE TABLE Orders 
(
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

//defining a FOREIGN KEY constraint on multiple columns

CREATE TABLE Orders
(
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) 
REFERENCES Persons(PersonID);

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) 
REFERENCES Persons(PersonID);

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
CHECK

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
CREATE TABLE Persons 
(
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

CREATE TABLE Persons 
(
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

ALTER TABLE Persons
ADD CHECK (Age>=18);

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes’);

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
DEFAULT

CREATE TABLE Persons 
(
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

CREATE TABLE Orders 
(
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes’;

ALTER TABLE Persons
ALTER City DROP DEFAULT;
INDEX

Indexes are used to retrieve data from the database very fast

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.
CREATE INDEX idx_lastname
ON Persons (LastName);

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

DROP INDEX index_name ON table_name;

ALTER TABLE table_name
DROP INDEX index_name;
AUTO INCREMENT

CREATE TABLE Persons 
(
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

//To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement

ALTER TABLE Persons AUTO_INCREMENT=100;

//we will NOT have to specify a value for the "ID" column (a unique value will be added automatically)

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen’);
VIEWS

A view always shows up-to-date data!
CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;

SELECT * FROM [Current Product List];
--------
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);

SELECT * FROM [Products Above Average Price];

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;

SELECT * FROM [Category Sales For 1997];

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = 'Beverages’;

//Updating a View

CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;

//Dropping a View
DROP VIEW view_name;

Comments

Post a Comment

Popular

PASSWORD MANAGER–DashLane

Image
Today I am going to introduce New Software called “ DASHLANE ”. This is one of the most useful softwares for lazy persons like me. Being student I  use many applications and register into too many websites according to my requirement. It is hard to remember that many passwords of all applications and websites. Dashlane is the software that saves all the passwords and make websites to auto login. After installing Dashlane on our computer, whenever we are trying to login in to our websites it will ask us to save login information of that website , once we saved that information to Dashlane, from second time when we open particular website, Dashlane will auto login in to that website without entering our credentials. We have to setup one Master Password when we are creating new Dashlane account. It willask us to set Master Password  while installing it on our computer. This is the security feature that enabled on...
Read more

USEFUL EXTENSIONS IN CHROME

Image
Extensions are totally different from Apps that are available in Chrome store. Extensions are part of chrome browser and extensions are not part of browser and they are other apps from google like chrome. Here I am showing you the some of the extensions that I found are interesting and useful while I am browsing Internet. Chrome Extensions 1.  AdBlock USE : It blocks all the unnecessary advertisements on various websites that irritates us while browsing. 2.  Craving Words USE : This extension will automatically save the words and their meaning when we search that word in google dictionary chrome extension or in google. 3.  Google Dictionary USE : We can easily search meaning of the particular word while we are reading any content from webpage, by simply clicking extension button . 4. Dash lane USE : It is not accessible in chrome store. But we can get this extension from application Dash lane on our PC. Dash lane application will...
Read more