CRUD

Question 1

Syntax for creating a database

Answer 1

Create database DATABASENAME;

Question 2

Is the mysql command case sensitive?

Answer 2

No

Question 3

TRUE OR FALSE:
The database name must follow the rules in naming variable

Answer 3

TRUE

Question 4

Is the semicolon a requirement in database?

Answer 4

Yes

Question 5

Display all the database in my sql

Answer 5

Show databases;

Question 6

Deleting the database

Answer 6

DROP database DATABASENAME;

Question 7

Creating a table

Answer 7

create table tablename(
field1 datatype1
field2 datatype2
field3 datatype3);

Question 8

Adding field to the table

Answer 8

alter TABLE TABLENAME add FIELD DATATYPE;

Question 9

Changing the data type or fieldname of the table

Answer 9

alter TABLE TABLENAME change OLDFIELD NEWFIELD DATATYPE;

Question 10

Setting a primary key of the table

Answer 10

alter table TABLENAME add primary key(FIELDNAME);

Question 11

Deleting a field

Answer 11

alter table TABLENAME drop FIELDNAME;

Question 12

SHOWING ALL TABLES IN THE DATABASE

Answer 12

SHOW TABLES;

Question 13

Renaming table

Answer 13

alter table ORIG_NAME rename NEW_NAME;

Question 14

Adding Record to Table

Answer 14

insert into TABLENAME values(VALUE1, VALUE2, VALUE3, ….);

Question 15

Inserting record to table example

Answer 15

insert into STUDENT values(‘101’,’Cris’,’Female’,15,5500)

Question 16

Used to view the record of the tables

Answer 16

select * from TABLENAME;

Question 17

column modifier is used to display leading zeros of a number based on the display width.

Answer 17

ZeroFill

Question 18

column modifier automatically increases the value of a column by adding 1 to the current maximum value.

Answer 18

Autoincrement

Question 19

How to use ZeroFill?

Answer 19

create table student( student_no varchar(20), firstname varchar(20),age Tinyint(4) Zerofill);

Question 20

Example on how to use AUTOINCREMENT?

Answer 20

create table student(student_no INT PRIMARY KEY AUTO_INCREMENT, firstname varchar(20));

Question 21

Editing Record

Answer 21

UPDATE tablename SET fieldname = value where fieldname = criteria

Question 22

What is being done?

UPDATE EMPLOYEE SET FirstName = ‘Crista’ where Emp_no = ‘101’

Answer 22

The FirstName of Emp_no 101 will be set to Crista

Question 23

What will happen?

UPDATE tbemployee SET FirstName = ‘Crista’

Answer 23

This command will change the first name of all record to Crista

Question 24

What will happen?

UPDATE tbemployee SET age = age + 2

Answer 24

All recorded will be updated by adding 2 to the age

Question 25

What will happen? UPDATE EMPLOYEE SET salary = salary + 500 where gender = ‘female’

Answer 25

All female employee will be updated by adding 500 to the salary.

Question 26

What will happen? UPDATE EMPLOYEE SET age = 19, salary = 6100 where emp_no = ‘102’;

Answer 26

The record of employee who have a employee number of 102 will be updated. The salary will be set to 6100 and age to 19

Question 27

Deleting Record

Answer 27

Delete from TABLENAME where CONDITION;

Question 28

What will happen? Delete from tbstudent where sn = ‘102 ’;

Answer 28

This command will delete an student who have a student number of 102

Question 29

What will happen? Delete from tbstudent where firstname =‘Karen’ and lastname =‘Lagasca’;

Answer 29

This command will delete an student who have a first name of Karen and last name of Lagasca

Question 30

What will happen? Delete from tbstudent where firstname like ‘m%’

Answer 30

This command will delete an student who have first name that start with letter m

Question 31

- A statement used to select data from a database. - The result is stored in a result table called the **result-set**

Answer 31

SELECT

Question 32

How to select ALL FIELDS of a table?

Answer 32

SELECT * FROM table_name;

Question 33

Use select command with selected field

Answer 33

SELECT column_name, column_name FROM table_name;

Question 34

- used to sort the result-set by one or more columns. - sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

Answer 34

SQL ORDER BY KEYWORD

Question 35

SQL ORDER BY KEYWORD SYNTAX

Answer 35

SELECT column_name,column_name FROM table_name ORDER BY column_name,column_name ASC/DESC

Question 36

What is being done? SELECT * from EMPLOYEE order by LASTNAME ASC

Answer 36

The records will be ordered alphabetically by last name in ascending order

Question 37

SELECT * from EMPLOYEE order by DEPT

Answer 37

This will display all the record that sorted by department in ascending order

Question 38

- clause used to filter records - used to extract only those records that fulfill a specified criterion.

Answer 38

THE SQL WHERE CLAUSE BY KEYWORD

Question 39

How to display all the records that have a position of manager in a table named employee?

Answer 39

SELECT * FROM employee WHERE position = ‘Manager’

Question 40

How to display all the record that have a department of ACC in employee table?

Answer 40

SELECT * FROM employee WHERE dept = ‘ACC’

Question 41

- can be used in SQL command especially in number - <, >,<=, >=, =, <>

Answer 41

Relational Operator

Question 42

Display All the record from employee table that have a salary greater than 10000

Answer 42

select * from employee where salary > 10000;

Question 43

- It is used to select values within a range. - Selects values within a range. The values can be numbers, text, or dates.

Answer 43

BETWEEN KEYWORD

Question 44

SYNTAX FOR BETWEEN KEYWORD

Answer 44

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Question 45

What will happen? select * from EMPLOYEE where AGE BETWEEN 20 AND 25;

Answer 45

This will display all the record that the age is between 20 and 25. Remember that 20 & 25 are included

Question 46

Display all the record from EMPLOYEE table that the FIRSTNAME start from C to G only and not the H

Answer 46

select * from EMPLOYEE where FIRSTNAME BETWEEN ‘C’ AND ‘H’;

Question 47

It allows you to specify multiple values in a WHERE clause.

Answer 47

IN KEYWORD

Question 48

IN KEYWORD SYNTAX

Answer 48

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

Question 49

WHAT WILL HAPPEN? select * from EMPLOYEE where age in (18, 25)

Answer 49

This will display all the record that the age is 18 or 25