Database meta information, views and administration

Question 1

How can you show all databases available

Answer 1

SHOW DATABASES;

Question 2

Which query shows all the info for all the catalogs and tables

Answer 2

SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

Question 3

which query shows all the tables in all a databases?

Answer 3

SHOW TABLES FROM database_name;

or

USE database_name
SHOW TABLES;

or

EXPLAIN table_name

Question 4

How can you see all the metadata of a table?

Answer 4

DESCRIBE table_name;

Question 5

what is an index?

Answer 5

a data structure that stores the values for a specific column.

e.g.
on column name.
now if you have to look for all John names you dont have to look at all rows. An index will store all column names alphabetically and you will only need to search in that

Question 6

How do you create an index?

Answer 6

CREATE INDEX name_index
ON User (Name)

Question 7

What is a view

Answer 7

It is a virtual table derived from other tables that contain data from those tables - a virtual table that refers to some data in a database.

Question 8

Does the DBMS store the views result or the definition of the view?

Answer 8

The definition

Question 9

How do you create a view?

Answer 9

CREATE VIEW view_name AS select_statement

Question 10

Does the view duplicate data?

Answer 10

No it has references to the real value.

Question 11

Why would you use a view rather than the normal tables?

Answer 11

To restrict access or to avoid complicated sql queries to get data.

Question 12

What are the problems with views?

Answer 12

have to be re-run anytime they are referenced because each time you use the name of a view, its table is recreated from existing data - a solution is to store the view as a temporary table making the view last a session - but you have to add the data to the table

Question 13

What is a temporary table

Answer 13

it is a temporary table that lasts for one session only- You have to create and add data to them every time.

Stored procedures can help you create them easily

Question 14

What happens when you update a view

Answer 14

The updates are retained in the database

Question 15

What happens when you update a temporary table

Answer 15

the updates are not saved anywhere else than in the temporary table

Question 16

How can you manage access to a DB?

Answer 16

1. authorization identifier: user & psw
2. ownership: can be granted or revoked to users
3. privileges: actions users are allowed to carry out on a DB

Question 17

How can you show all the users?

Answer 17

SELECT user FROM mysql.user;

Question 18

How can you show users, hosts they can connect from and password?

Answer 18

SELECT user,host,password FROM mysql.user;

SELECT * FROM mysql.user;

Question 19

How can you show all the privileges given to a user?

Answer 19

SHOW GRANTS FOR USER;

Question 20

How do you grant and revoke privileges?

Answer 20

GRANT INSERT on Employee

to receptionist

Question 21

How can you show the current user logged in?

Answer 21

SELECT current_user();

Question 22

How can you log into a user in MySQL?

Answer 22

In Workbench, when you chose the database to connect to, create a new connection and put in the user and password instead of root.

Question 23

How do you create users?

Do you need a privilege?

Answer 23

You need the CREATE USER and GRANT privileges.

The syntax is:
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘user_password’;

Question 24

How can you modify users?

Do you need a privilege?

Answer 24

With the ALTER USER statement:
ALTER USER ‘user’@’hostname’ IDENTIFIED BY ‘newPass’;

You need the CREATE USER or the UPDATE privilege for the MYSQL system schema

Question 25

What is a role in SQL and why do you use them

Answer 25

It is a named group of related privileges that can be granted to the user.

You use them to make the process of granting a user privileges

Question 26

What is the syntax for creating a role?

Privileges?

Answer 26

CREATE ROLE role_name

global CREATE ROLE or CREATE USER privilege, as well as the CONNECTION_ADMIN privilege

Question 27

What is a block statement?

Answer 27

It is a block of multiple statements that are enclosed by BEGIN and END

Question 28

What are the conditional constructs?

Answer 28

IF, CASE

Question 29

What are the Loop consturcts?

Answer 29

WHILE, REPEAT, LOOP