-
How to create a new database
- DROP DATABASE IF EXISTS db_Name;
- CREATE DATABASE db_Name;
- USE db_Name;
-
how to create a new table
- DROP TABLE IF EXISTS fan;
- CREATE TABLE fan
- (
- FanId INT PRIMARY KEY
- AUTO_INCREMENT,
- FanName VARCHAR(40),
- TeamId INT,
- FOREIGN KEY (TeamId)
- references team(TeamId)
- );
-
9. Delete all employees whose first names start with the letter ‘M’ from the employees table.
- DELETE FROM employees
- WHERE emp_first_name LIKE 'M%';
-
4. Write a statement to give Henry a $5 an hour raise.
- UPDATE employees
- SET emp_hourly_pay = emp_hourly_pay + 5
- WHERE emp_first_name = "Henry";
-
What are the aggregate functions?
- COUNT ( [ALL|DISTINCT] expression)
- COUNT (*)
- AVG ( [ALL|DISTINCT] expression)
- SUM ( [ALL|DISTINCT] expression)
- MAX ( [ALL|DISTINCT] expression)
- MIN ( [ALL|DISTINCT] expression)
-
summary data is group
- SELECT selection_list
- FROM table
- [ WHERE search_conditions ]
- [ GROUP BY group_by_list ]
- [ HAVING search_condition ]
- [ ORDER BY order_by_list ]
-
Write the statement to get author_id, first_name, and last_name for all authors whos last name is Anderson regardless of case.
- SELECT author_id, first_name, last_name
- FROM authors
- WHERE UPPER(last_name) = 'ANDERSON';
-
Write the select statement to concatenate first and last names
- SELECT CONCAT(FIRSTNME, LASTNAME)
- FROM DSN8A10.EMP;
-
basic query
- SELECT column1, column2....columnN
- FROM table_name
- WHERE CONDITION
- ORDER BY column_name {ASC|DESC};
-
9. Delete all employees whose first names start with the letter ‘M’ from the employees table.
- DELETE FROM employees
- WHERE emp_first_name LIKE 'M%';
-
The table VARS has the follow Fields and rows:
num, VAR1, VAR2
3, 'H', 'H'
4, 'K', null
6, null, 'G'
Insert rows with values
2, E and null
7, null, null.
- INSERT INTO VARS(num, VAR1, VAR2) VALUES
- (2,'E',null),
- (7,null,null);
-
How do you create and select the database called review?
- DROP DATABASE IF EXISTS review;
- CREATE DATABASE review;
- USE review;
-
How do you create a table called person with the fields: id, fname, lname, height?
- DROP TABLE IF EXISTS person;
- create table person
- ( id int,
- fname char(5),
- lname char(5),
- height int(3)
- );
-
The table person has the fields: id(integer), fname(5), lname(5) and height(3). Add rows to the table.
- insert into person(id, fname, lname, height) values
- (1, 'Joe', 'Smith', 182),
- (2, 'Kim', 'Good', 175),
- (3, 'Mark', 'Allan', 168),
- (4, 'Sam', null, 187),
- (5, 'Bob', 'Claw', null),
- (6, 'David', 'Ellie', 174)
- ;
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
Delete the person with id 3.
- delete from person
- where id = 3;
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
Change Marks height to 186.
- update person
- set height = 186 where id=3;
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
Add 3 to all people with id <4.
- update person
- set height = height +3
- where id < 4;
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
Change Sam's lname to Apple
- update person
- set lname = 'Apple'
- where fname = 'Sam';
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
Delete everyone shorter than 175.
- delete from person
- where height < 175;
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
Find all the row with names that start with J.
- Select * FROM PERSON
- WHERE LEFT(FNAME,1) = 'J'
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
-
The person table has the following rows.
1, 'Joe', 'Smith', 182
2, 'Kim', 'Good' , 175
3, 'Mark', 'Allan' , 168
4, 'Sam', , 187
5, 'Bob', 'Claw' ,
6, 'David', 'Ellie' , 174
|
|