SQL
What do the wild cards % and _ mean
% = any number of characters _ = one character
More about views
A relational view has several special characteristics: You can use the name of a view anywhere a table name is expected in a SQL statement. Views are dynamically updated. That is, the view is re-created on demand each time it is invoked. Therefore, if new products are added or deleted to meet the criterion P_PRICE > 50.00, those new products will automatically appear or disappear in the PRICEGT50 view the next time the view is invoked. Views provide a level of security in the database because they can restrict users to seeing only specified columns and rows in a table. For example, if you have a company with hundreds of employees in several departments, you could give each department secretary a view of certain attributes only for the employees who belong to that secretary's department. Views may also be used as the basis for reports. For example, if you need a report that shows a summary of total product cost and quantity-on-hand statistics grouped by vendor, you could create a PROD_STATS view as:
Add columns HIRE_DATE and DOB of DATE type in the employee table
ALTER TABLE EMPLOYEE ADD COLUMN HIRE_DATE DATE, ADD COLUMN DOB DATE;
Make EMP_ID a primary key in the table employee
ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPID PRIMARY KEY (EMP_ID);
Make EMP_ID a primary key:
ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPID PRIMARY KEY (EMP_ID);
Add comment to HIRE_DATE column: 'Employee Hire Date' Table is called employee
ALTER TABLE EMPLOYEE MODIFY COLUMN HIRE_DATE DATE COMMENT 'EMPLOYEE HIRE DATE';
Add comment to Employee table: 'This table hold employee data'
ALTER TABLE EMPLOYEE COMMENT 'THIS TABLE HOLD EMPLOYEE DATA';
When you want to add a new column to an existing table, do you use modify, update or alter TABLE NAME?
ALTER TABLE EMPLOYEES ADD product VARCHAR(32)
When you want to drop a column, do you use modify, update or alter TABLE NAME?
ALTER TABLE EMPLOYEES DROP COLUMN Color;
change table PART add primary key part_code add foreign key v_code that references vendor
ALTER TABLE PART ADD PRIMARY KEY (PART_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;
Changing a Column's Data Characteristics Change the table PRODUCT Edit the existing column MODIFY to be Decimal with 9 total digits and 2 digits after the decimal
ALTER TABLE PRODUCT MODIFY (P_PRICE DECIMAL(9,2));
Adding a column into a table table name is product column is salecode char 1
ALTER TABLE PRODUCT _SALECODE CHAR(1);
You have to change the "pass_key" column of blob data type to include the comment "id of employees" in the Pass_keys table.
ALTER TABLE Pass_keys MODIFY pass_key blob COMMENT 'id of employees';
Add the "date_added" column in the Places table that automatically holds the timestamp of when the row was created.
ALTER TABLE Places ADD date_added timestamp default current_timestamp;
To add a comment to a column, you would use both the ALTER and MODIFY functions:
ALTER TABLE [table name] MODIFY [full column definition] COMMENT 'This is my column comment';
Change the popularity column of INT(11) data type to include the comment "Popularity based on 2019 sales" in the MenuItems table.
Alter table MenuItems Modify column popularity Int(11) Comment 'Popularity based on 2019 sales';
You have to add the "created" column in the Pass_keys table that automatically holds the timestamp of when the row was created.
Alter table Pass_keys Add column created Timestamp default current_timestamp;
You have to change the data type of "pass_key" to blob data type (this column should not be NULL) in the Pass_keys table.
Alter table Pass_keys Modify column pass_key BLOB not null;
Add the "date_added" column in the Places table that automatically holds the timestamp of when the row was created.
Alter table Places Add column date_added Timestamp default current_timestamp;
Change the table emp Add comment on the column hiredate (date type), saying 'hello'
Alter table emp Modify column hiredate date Comment'Employee date of hire';
You need to delete a column but it is the foreign key, what do you do?
Alter table employee Drop Foreign Key Emp_id;
Say you want to set an existing row to a new row- like change all rows with '34' to '40' Do you use alter, modify or update TABLE NAME?
UPDATE UPDATE PRODUCT SET P_SALECODE = '2' WHERE P_CODE = '1546-QQ2';
update the table product make salecode = 2 where code = 1546
UPDATE PRODUCT SET SALECODE = '2' WHERE CODE = '1546';
Change the major from Biology to Bio in students where the major is Biology Table is named student
UPDATE student SET major = 'Bio' WHERE major = 'Biology'
Change the major from Biology to Bio in students with student_id of 4 Table is named student
UPDATE student SET major = 'Bio' WHERE student_id = 4;
Change the major from Biology to Biochemistry in students where the major is Biology OR chemisty Table is named student
UPDATE student SET major = 'Biochemistry' WHERE major = 'Biology' OR major = 'Chemistry';
Update the chart so that everyone with a student_id of 1 is named Tom and has an undecided major
UPDATE student SET name = 'Tom', major = 'Undecided' WHERE student_id = 1;
Change all notes to be "large animal" and LastWeighDate to be 2019-08-10 in the Animals table where the Weight is more than 100 and Gender is M.
Update Animals Set notes = 'large animal', LastWeighDate = '2019-08-10' Where weight > 100 and Gender = 'M';
Update TakeOut as Y in the MenuItems table where the Cost is more than 9.95.
Update MenuItems Set TakeOut = 'Y' Where cost > 9.95;
Change the price for all the Camaro model to $40,000 in the for_sale table. (price is the variable)
Update for_sale Set price = 40000;
What's a BLOB?
We've come a long way in computing power since the days when computers filled entire rooms. It's now possible to store and retrieve huge chunks of data. One of those chunks is really a shapeless, undefinable entity: a BLOB. It's not text, or numbers, or dates and times. You might think this is science fiction, but it's very real. Technically a Binary Large Object (BLOB) is an object data type, meaning it refers to an object. Unlike a character or integer data type, the object data type only contains a pointer or reference to the value of the object. A BLOB can hold a very large block of data, anything from documents to images to videos. You could store your great American novel in a BLOB if you really wanted to (as a file).
know
Where a= 2 you change c to "even funnier"
Count all the rows from the country table
Count (*) From country;
Create a table called test with 3 columns a- INT b- TEXT c- text Insert the following values into the row (1, 'This', 'Here');
Create table Test (a INT, b text, c text) Insert into Test Values (1, 'This', 'Here');
Create a view called V_Customer Select the name and family columns from the employees table
Create view V_customer AS SELECT name, family FROM Employees;
All date and time types For example Birth_date DATE Arrival_time Time
DATE TIME DATETIME TIMESTAMP YEAR?
declare the variable test as an int
DECLARE @test INT
· Delete ALL rows from the table student
DELETE FROM STUDENT;
What does this mean
Zero or many
What does this mean
Zero or one
do
create table acquisitions( ID INT auto_increment Primary Key, date_aquire Date, country VARCHAR(32), amountpaid decimal (6,2), comments test );
create a table called my_library with the book_id as the primary key- all columns should require a value book_id integer author_name (string, 20 characters) book_title (string, 40 characters) price (a decimal value, assuming that all books cost less than $100)
create table my_library ( book_id INT not null, author_name VARCHAR (20) not null, book_title VARCHAR (40) not null, price double (4,2), Primary key (book_id) );
do
create table places_table ( id INT auto_increment Primary key, country VARCHAR (32)); Insert into Places_table (country) Select distinct country from ACQ; Alter table ACQ Drop country; Alter table ACQ ADD place_id INT;
Auto increment so you don't have to write student_id 1,2,3,4 etc. for each entry create table student ( student_id INT PRimary key, name VARCHAR(25), major VARCHAR (20), );
create table student ( student_id INT AUTO_INCREMENT, name VARCHAR (20), major VARCHAR (20), PRIMARY KEY (student_id) );
Use the attached chart Make it so if their is no major, the default major we specify in creating a table is used- which is undecided
create table student ( student_id INT PRimary key, name VARCHAR(25), major VARCHAR (20) DEFAULT 'Undecided', );
Trigger example
delimiter // CREATE TRIGGER TRG_TOUR_AUDIT BEFORE UPDATE ON TOURS FOR EACH ROW BEGIN INSERT INTO TOUR_AUDIT (OLD_VALUE) VALUES (OLD.TOUR_MEMBERS); END IF; END;// delimiter;
Make it so that major has to be unique for each entry create table student ( student_id INT PRimary key, name VARCHAR(25), major VARCHAR (20), );
major VARCHAR (25) UNIQUE,
Make it so that "Name" cannot have a null value create table student ( student_id INT PRimary key, name VARCHAR(25), major VARCHAR (20), );
name VARCHAR (20) NOT NULL,
Find all countries that have 'a' as the second letter with anything following- the table is called countries and the countries are ordered by name
select name from countries where name LIKE '_a%';
return the names of animals (name) and the year they were last weighed (lastweightdate), for any animal whose name begins with F
select name, year(lastWeightDate) AS yearlastweighted from animals where left (name, 1) = 'F';
How to display a view?
show views like 'my_view';
Change all menu items DateAdded dates to 2019-08-10 in the MenuItems table.
update MenuItems Set DateAdded = '2019-08-10';
Create the following table Create a table called employee emp_id INT PRIMARY KEY, first_name VARCHAR (40), last_name VARCHAR (40), birth_day DATE, sex VARCHAR (1), salary INT, super_id INT, branch_id INT create a table called branch branch_id INT PRIMARY KEY, branch_name VARCHAR (40), mgr_id INT, mgr_start_date DATE, MAKE the foreign key (mgr_id) and have it references emp_id on the employee table- specify on delete set null ): Alter the table employee- add foreign key (branch_id) which references branch_id in the table branch- set to null on delete
DROP TABLE student; create table employee ( emp_id INT PRIMARY KEY, first_name VARCHAR (40), last_name VARCHAR (40), birth_day DATE, sex VARCHAR (1), salary INT, super_id INT, branch_id INT ); CREATE TABLE branch ( branch_id INT PRIMARY KEY, branch_name VARCHAR (40), mgr_id INT, mgr_start_date DATE, FOREIGN KEY (mgr_id) REFERENCES employee (emp_id) ON DELETE SET NULL ); ALTER TABLE employee ADD FOREIGN KEY (branch_id) REFERENCES branch (branch_id) ON DELETE SET NULL;
· Delete the rows where student_id is 5 in the table called students
Delete FROM student Where student_id = 5;
Grant Example on the table employee to John
GRANT SELECT ON EMPLOYEE TO JOHN;
The for_sale table structure is same as the garage table. You have to copy all of the "Alfa Romeo" cars from the garage table to the for_sale table. Use make, model, and year variables
INSERT INTO for_sale (make, model, year) SELECT * FROM garage Where make = 'Alfa Romeo'
We created a table named student with the following empty values: Student ID INT Name VARCHAR Major VARCHAR Insert the following values into the table called student: Student ID 1, Name = jack , Major = Biology
INSERT INTO student VALUES (1, 'Jack', 'Biology');
TRUNCATE
If you don't use a WHERE clause with DELETE, all records will be deleted. It can be very slow in a large table, especially if the table has many indexes. If the table has many indexes, you can make the cache larger to try making the DELETE faster (key_buffer_size variable). For indexed MyISAM tables, in some cases DELETEs are faster if you specify the QUICK keyword (DELETE QUICK FROM ...). This is only useful for tables where DELETEd index values will be reused. TRUNCATE will delete all rows quickly by DROPping and reCREATE-ing the table (not all Storage Engines support this operation). TRUNCATE is not transaction-safe nor lock-safe. DELETE informs you how many rows have been removed, but TRUNCATE doesn't. After DELETing many rows (about 30%), an OPTIMIZE TABLE command should make next statements faster. For a InnoDB table with FOREIGN KEYs constraints, TRUNCATE behaves like DELETE.
Add a new species, with the characteristics listed below, to the species table Name: Chimpanzee Description: A tree animal ConservationStatus: 3
Insert into Species ( name, description, convservationStatus values ('Chimpanzee', 'A TRee animal', 3 );
CHAR Datatype:
It is a datatype in SQL which is used to store character string of fixed length specified. If the length of string is less than set or fixed length then it is padded with extra blank spaces so that its length became equal to the set length. Storage size of CHAR datatype is of n bytes(set length). We should use this datatype when we expect the data values in a column are of same length.
VARCHAR Datatype:
It is a datatype in SQL which is used to store character string of variable length but maximum of set length specified. If the length of string is less than set or fixed length then it will store as it is without padded with extra blank spaces. Storage size of VARCHAR datatype is equal to the actual length of the entered string in bytes. We should use this datatype when we expect the data values in a column are of variable length.
What does this mean
Many
What are images > 1 mb stored as
Medium blob
What does this mean
One
What does this mean
One and only one
What does this mean
One or many
Example of ENum
Priority ENUM ("low', 'medium', 'high');
Revoke Example on the table employee to John
REVOKE SELECT ON EMPLOYEE FROM JOHN;
What does a natural join return?
Returns only the rows with matching values in the matching columns; the matching columns must have the same names and similar data types
Find the average of affected_customers saved as avg_affected from the table grid
SELECT AVG(affected_customers) AS ave_affected FROM grid;
Count the distinct values of the column affected_customers, save as unique_count from the table grid
SELECT COUNT (DISTINCT affected_customers) AS unique_count FROM grid;
Calculate the total amount of the column affected_customers and save as total_affected from the table grid
SELECT SUM (affected_custoers) AS total_affected FROM grid;
Change the appearance of the column named grey to blue in the table employees
SELECT 'grey' AS 'Blue' FROM employees;
Find any employee born in October Table employee birth_date is the variable Remember the date format is 1970-10-24 Year, month, day
SELECT * FROM employee WHERE birth_date LIKE '____-10%'; · Remember the date format is 1980-10-10 (year, month, day) · To find anyone born in October, you use 4 _ to indicate any four digit number is okay · Then you use a 10% sign, since October is the 10th month- the % means any characters can follow- what follows isn't important
SELECT name from the table student where name is not equal to jack
SELECT * FROM student WHERE name <> 'Jack';
SELECT name from the table student where name is IN Kate or Claire
SELECT * FROM student WHERE name IN ('kate', 'Claire');
SELECT only two rows from the table student
SELECT * FROM student LIMIT 2;
ANOTHER WAY TO NATURAL JOIN
SELECT * From EMP, DEP WHERE EMP.ID = DEP.ID;
Find the AVERAGE of all employee salaries of all males (sex)
SELECT AVG (salary) FROM employee WHERE sex = 'M';
Find the AVERAGE of all employee salaries
SELECT AVG (salary) FROM employee;
You have a table with a list of continents. How do you return all the unique continent names? Table is country, continent is continent
SELECT COUNT (distinct continent) FROM Country;
You have a table with employees How do you find out the number of employees in the table?
SELECT COUNT (emp_id) FROM employee
Find the number of female employees born after 1970 from the table employee Use sex and birth_date columns
SELECT COUNT (emp_id) FROM employee WHERE sex = 'F' and birth_date > '1970-01-01';
Find out how many males and females there are in the table employee
SELECT COUNT (sex), sex FROM employee GROUP BY sex;
Unique values select the distinct values of code from the table product
SELECT DISTINCT CODE FROM PRODUCT;
There is a column called Sex in the table employee You want to return all unique, or distinct, values in the sex column. How do you do this?
SELECT DISTINCT sex FROM employee;
List all employees born in 1972
SELECT Emp_DOB TO_CHAR(Emp_DOB, 'YYYY') AS Year FROM Employee Where TO_CHAR(Emp_DOB, 'YYYY') = 1972 OR YEAR (Emp_DOB) AS YEAR WHERE YEAR = 1972
Perform left join Table 1 - T1 Table 2 - T2 Select Name and Age Perform the join on T2
SELECT Name, age FROM T1 LEFT OUTER JOIN T2 ON T1.C1=T2.C1
CASE Select OrderID and Quantity from OrderDetails If the quantity is over 30 then return 'The quantity is greater than 30' If the quantity is 30 then return 'The quantity is 30' If neither of the above return 'The quantity is under 30'
SELECT OrderID, Quantity CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30" END FROM OrderDetails;
Doing an INNER JOIN First, select all the variables you want displayed Next, select one of the tables with a FROM statement Next, select the other table with an INNER JOIN statement Last, match the matching data with an ON statement followed by the similar columns seperated by an =
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Find the sum of all employee's salaries from the table employee salary
SELECT SUM (salary) FROM employee;
Select the top 5 artist from the table artist
SELECT TOP (5) artist FROM artists;
Select customer_id and total from the table invoice where the total is between 20 and 30
SELECT customer_id, total FROM invoice WHERE total BETWEEN 20 AND 30;
How would you get a listing of all employees from the employee tables and all the branches from the branch table? employees names = first_name from employee table branches = branch_name from branch table
SELECT first_name FROM employee UNION SELECT branch_name FROM branch; You get one list- first listing all the employees, then listing all the branch names- but only using one list
Select only the names from the table student
SELECT name FROM student;
Only return the first five results in the table students with the category name
SELECT name FROM students LIMIT 5;
SELECT name and major from the table student and order them by the major and student_id
SELECT name, major FROM student ORDER BY name;
SELECT name and major from the table student and order them by the name
SELECT name, major FROM student ORDER BY major, student_id;
SELECT name and major from the table student and order them by the name in descending order
SELECT name, major FROM student ORDER BY name; DESC
Select song from the table songlist where the song begins with the letter a
SELECT song FROM songlist WHERE song LIKE 'a%';
Select the song and artist from the table songlist where there artist is either van halen or zztop (USE IN)
SELECT song, artist FROM songlist WHERE artist IN ('Van Halen', 'ZZ Top');
Select the song and artist from the table songlist where the release_year is 1994 or the release year is greater than 2000
SELECT song, artist FROM songlist WHERE release_year = 1994 OR release_year > 2000;
To view the table comment in Summary table
SHOW CREATE TABLE Summary;
How to show a view called V_Customer?
SHOW CREATE VIEW `V_Customer`;
To view the comment added to the column, use the following syntax:
SHOW FULL COLUMNS FROM [table name];
How to view a comment?
SHOW FULL COLUMNs from emp;
Find any clients who are in an LLC Table named Client Variable named client_name
Select * FROM Client WHERE client_name LIKE '%LLC'; (If it's any number of characters with an LLC at the end)(HAS TO END IN LLC)
Select all rows from the table country
Select * From country;
Display the current date plus 1
Select Current_DATE() + 1;
Display the current date
Select Current_DATE();
Display the current time
Select Current_Time();
Take the date from '2017-06-15'
Select DATE('2017-06-15');
Use Date Format with to format the current date with month, day and year
Select Date_Format(curdate(), '%m %d %y');
Take the month from '2017-06-15' using extract
Select Extract(Month from '2017-06-15');
Round 24.124 to 2 decimal places
Select ROUND(24,124, 2);
Display the current timestamp
Select current_timestamp();
Format the date showing only the year, day and month '2017-06-15' Using DAte format
Select date_format('2017-06-15', '%M %d %Y');
Format the date showing only the year, '2017-06-15' Using DAte format
Select date_format('2017-06-15', '%Y');
Find all branches and the names of their managers USE LEFT JOIN You want the column headings that print out to be: emp_id, first_name from the table employee AND branch_name from the table branch You want to combine employee and branch tables only where the employee.emp_id is equal to branch.mgr_id
Select employee.emp_id, employee.first_name, branch.branch_name FROM employee LEFT JOIN branch ON employee.emp_id = branch.mgr_id;
Find all branches and the names of their managers using INNER JOIN You want the column headings that print out to be: emp_id, first_name from the table employee AND branch_name from the table branch You want to combine employee and branch tables only where the employee.emp_id is equal to branch.mgr_id
Select employee.emp_id, employee.first_name, branch.branch_name (these are the columns that print out) FROM employee JOIN branch ON employee.emp_id = branch.mgr_id; (combine as long as the emp_id and mgr_id are the same) · IF emp_id = mgr_id we want to select the variables from the select statement to display
You have two variable names, first_name and last_name, you want to select them but display first_name as forename and last_name as surname from the table employee
Select first_name AS Forename, last_name AS Surname From employee;
How to call a function and procedure?
Select function_name(1); Call procedure_name();
Return gender and the count for each gender, titles as count, making sure the female rows are listed first
Select gender, count(*) AS count from animals group by gender order by gender;
Select island with any amount of letters before or after from the table countries
Select island From countries Where island LIKE '%island%';
List all the makes that have more than 3 models in them. FROM GARAGe
Select make FROM garage Group by make Having count(model) > 3 ;
Write a query to retrieve the make and do a count of all the models of each make with model year after 1950. FROM Garage
Select make, count(model) FROM garage Where year > 1950 Group by make ;
Use a regular expression to search for country names where the second letter begin with a,b,c or d table is country country names are name
Select name from country Where name REGEXP '^.[a-e].*'; The ^ means you are starting a new line The . means that the first character can be any letter The end . means any character again
List all the regions that have more than two cities in them. From PLACES table
Select region FROM Places Group by region Having count(city) > 2 ;
Why use the VIEW function?
So you can save the database to use at a later time. It is based off the SELECT statements
And more about views
The most common updatable view restrictions are as follows: GROUP BY expressions or aggregate functions cannot be used. You cannot use set operators such as UNION, INTERSECT, and MINUS. Most restrictions are based on the use of JOINs or group operators in views. More specifically, the base table to be updated must be key-preserved, meaning that the values of the primary key of the base table must still be unique by definition in the view.
What does SELECT Count return?
The number of rows Ex- SELECT Count (*) FROM countries Returns the number of rows in the table countries
Database Management System can CRUD
CREATE, READ, UPDATE, DELETE
What is an IP address stored as?
Binary or Varbinary
Calling a stored function
CALL GetCustomerLevel(-131,@customerLevel); SELECT @customerLevel;
CASE example syntax
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE resultEND;
Session ID: Store as
CHAR()
Create a stored procedure NAme SelectAllCustomers Then execute it
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) AS SELECT * FROM Customers WHERE City = @City GO; Execute EXEC SelectAllCustomers @City = 'London';
Steps for writing a procedure 1. Specify the delimiters 1. First you must CREATE the procedure and NAME IT 2. Do you want to accept input or not? If you do, use an IN statement with a variable -if not put () after the prodedure name 3. BEGIN the procedure Then you must ask what exactly you want the procedure to do After this you end it followed by the new delimiter Lastly, Call back the original delimiter
CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT) BEGIN SELECT COUNT(*) INTO cities FROM world.city WHERE CountryCode = country; END//
What format are dates in
YYYY-MM-DD
