SQL

Question 1

Types of databases ?

Answer 1

• Relational Dabases : use SQL language and store data in Tables with columns and rows.
  
  • Non-Relational Databases ( noSQL ) : stores data using other data structures , key-value , JSON documents , XML documents, Graphs …

Question 2

what is RDBMS ?

Answer 2

mySQL, Oracle , postgreSQL, mariaDB…It performs CRUD operations and makes it easy to create , maintain and secure a DB.

Question 3

what is NRDBMS ?

Answer 3

mongoDB, dynamoDB, firebase ,…

Question 4

What is SQL ?

Answer 4

• It is a language for interacting with RDBMS
  
  • Used for CRUD operations and other administrative tasks (security , backup …)
  • It is a hybrid language : DQL+DDL+DCL+DML
  • DQL: Used to query DB and get information from the DB
  • DDL : defines DB schemas
  • DCL: control access to the data in the DB
• DML : Insert, update and delete data from the DB

Question 5

What is a database query ?

Answer 5

A query is a request made to the RDBMS for a specific information .(A google search is a query )

Question 6

What is a primary key ?

Answer 6

It is an attribut which uniquely identifies a row in the DB

Question 7

what is a foreign key ?

Answer 7

It stores the primary key of another DB table , allows us to define relationships between databases

Question 8

What are the data types in SQL ?

Answer 8

INT
DECIMAL(m,n): //m is the number of digits and n is the number of digits after the decimal
VARCHAR(n): n is the strength of the string
BLOB : for images and files and large objects
DATE: ‘yyyy-mm-dd’
TIMESTAMP :’yyyy-mm-dd hh:mm:ss’

Question 9

What is the command to create a database named giraffe ?

Answer 9

CREATE DATABASE girrafe;

Question 10

What is the command to create a table named student with student_id , name and major attributes ?

Answer 10

CREATE TABLE student (
		Student_id INT PRIMARY KEY,               OR
		Name VARCHAR(20),
		Major VARCHAR(20)
	) ;

Question 11

What is the command to delete a table named student ?

Answer 11

DROP TABLE student;

Question 12

how to create a new column named gpa in the student table ?

Answer 12

ALTER TABLE student ADD gpa DECIMAL(3,2); //it creates a new gpa column

Question 13

How to delete the gpa column from the student table ?

Answer 13

ALTER TABLE student DROP COLUMN gpa;

Question 14

How to display the structure of the table ?

Answer 14

DESCRIBE student ;

Question 15

• How to insert a row with id 1 , name jack and major biology
• How to insert a row with id 1 , name jack.

Answer 15

INSERT INTO student VALUES (1,’jack’,biology);
or
INSERT INTO student (student_id,name) VALUES(‘1,jack’)

Question 16

How to display the content of a student table

Answer 16

SELECT * FROM student ;

Question 17

Create a table with the use the NOT NULL , DEFAULT,UNIQUE constraints
and insert a row

Answer 17

CREATE TABLE student (
Student_id INT PRIMARY KEY ,
Name VARCHAR(20) NOT NULL,
Major VARCHAR(20) UNIQUE,
FavoriteClass VARCHAR(20) DEFAULT ‘undecided’
);
INSERT INTO student(student_id,name,major) VALUES(1,’Jack’,’science);

Question 18

Use the AUTO INCREMENT constraint and insert 3 rows in the student table

Answer 18

CREATE TABLE student (
Student_id INT PRIMARY KEY AUTO_INCREMENT ,
Name VARCHAR(20),
Major VARCHAR(20)
);
INSERT INTO student( name ,major ) VALUES( ‘Jack’, ‘science’ );
INSERT INTO student( name ,major ) VALUES( ‘Kate’, ‘math ‘ );

Question 19

How to

- ->Change the biology major to Bio:

- ->Change the bio and chemistry majors to Biochemistry.
- ->Change the name and major of a specific row/student exp with the id of 6.
- ->Change all the majors to nothing  ( in all the rows) :

Answer 19

UPDATE student SET major=’bio’ WHERE major=’biology’;
UPDATE student SET major=”Biochemistry” WHERE major=’bio’ OR major=’chemistry’;
UPDATEstudentSETname=’Tom’,major=’undecided’WHEREstudent_id=6;

UPDATE student SET major='nothinng';