SQL /MySQL

Question 1

What is SQL?

Answer 1

SQL stands for Structured Query Language and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion, and deletion of data from a database.

Question 2

What is RDBMS?

Answer 2

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data store into the tables.

Question 3

What is DBMS?

Answer 3

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

Question 4

What is a primary key?

Answer 4

A combination of fields which uniquely specify a row. This is a special kind of unique key and it has an implicit NOT NULL constraint. Primary key values cannot be NULL.

Question 5

What is a Database?

Answer 5

An organized form of data for easy access, storing, retrieval, and managing of data. A structured form of data which can be accessed in many ways.

Question 6

What are tables and fields?

Answer 6

A table is a set of data that are organized in a model with Columns and Rows. Columns are vertical and Rows are horizontal. A table has a specified number of columns called fields but can have any number of rows which are called a record.

Question 7

What is a unique key?

Answer 7

A unique key constraint uniquely identifies each record in the database. This provides uniqueness for the column or set of columns.

A Primary key constraint has automatic unique constraint defined on it.

There can be many unique constraint defined per table, but only one Primary key constraint defined per table.

Question 8

What is a foreign key?

Answer 8

A foreign key in one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

Question 9

What is a Join?

Answer 9

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

Question 10

What is an Inner Join?

Answer 10

An inner join returns rows when there is at least one match of rows between the tables.

Question 11

What is a Right Join?

Answer 11

Right join returns rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table.

Question 12

What is a Left Join?

Answer 12

Left join returns rows which are common between the tables and all rows of Left hand side table. it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.

Question 13

What is a Full Join?

Answer 13

Full join returns rows when there are matching rows in any one of the tables. It returns all the rows from the left hand side table and all the rows from the right hand side table.

Question 14

What is a relationship and what are they?

Answer 14

Database Relationships is defined as the connection between the tables in a database. The various data basing relationships are:

• One to One Relationship
• One to Many Relationship
• Many to One Relationship
• Self-Referencing Relationship

Question 15

What is a Query?

Answer 15

A Query is a question to the database. A DB query is code written in order to get information back from the database.

Question 16

What is a stored procedure?

Answer 16

Stored Procedure is a function that consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure that can be executed at any time whenever required.

Question 17

What is a constraint?

Answer 17

Constraint can be used to specificy the limit on the data type of the table. Constraint can be specified while creating or altering the table statement. Sample of constraints include:

• NOT NULL
• CHECK
• DEFAULT
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY

Question 18

What is Auto Increment?

Answer 18

Auto Increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used whenever PRIMARY KEY is used.

Question 19

What is CLAUSE?

Answer 19

SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.

Ex:
Query that has WHERE condition
Query that has HAVING condition.

Question 20

What is an ALIAS command?

Answer 20

ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.

Question 21

What is the difference between TRUNCATE and DROP statements?

Answer 21

TRUNCATE removes all the rows from the table and it cannot be rolled back. DROP removes a table from a database and cannot be rolled back.

Question 22

How to select unique records from a table?

Answer 22

Select unique records from a table by using DISTINCT keyword.

SELECT DISTINCT StudentID, StudentName from Student

Question 23

What is MySQL?

Answer 23

MySQL s an open source relational DBMS which is built, supported, and distributed by MySQL AB (now acquired by Oracle)

Question 24

Why is MySQL used?

Answer 24

MySQL database server is reliable, fast and very easy to use. It is a pure relational database.

Question 25

What is the default port for MySQL?

Answer 25

3306

Question 26

What is meant by % and _ in a LIKE statement?

Answer 26

% corresponds to 0 or more characters, _ is exactly one character

Question 27

How can we get the number of rows affected by a query?

Answer 27

SELECT COUNT (user_id) FROM users;

Question 28

How do you connect to MySQL via the command line?

Answer 28

mysql -u (username) -p

Question 29

How do you connect to a specific DB in MySQL at login via the command line?

Answer 29

mysql -u (username) -p (database)

Question 30

How do you exit MySQL in the command line?

Answer 30

exit;

Question 31

How do you create a database with a specified name ?

Answer 31

CREATE DATABASE database_name;

Question 32

How do you choose a database or change the current database to another database?

Answer 32

USE database_name;

Question 33

How do you delete a database?

Answer 33

DROP DATABASE database_name;

Question 34

How do you show all databases in the current MySQL DB server?

Answer 34

SHOW DATABASES;

Question 35

How do you view all tables in a current DB?

Answer 35

SHOW TABLES;

Question 36

How do you create a table?

Answer 36

CREATE TABLE table_name(
column_name column_type
);

Question 37

How do you add a new column into a table?

Answer 37

ALTER TABLE table
ADD [COLUMN] column_name column_definition [FIRST | AFTER existing_column]

Can add the column as the first column or after a specific column. If the position is not specified, it will be added last.

Question 38

How do you drop a column from a table?

Answer 38

ALTER TABLE table_name

DROP [COLUMN] column_name;

Question 39

How do you show the columns of a table?

Answer 39

DESCRIBE table_name;

Question 40

How do you delete a table?

Answer 40

DROP TABLE table_name;

Question 41

How do you add a Primary Key to a table?

Answer 41

ALTER TABLE table_name

ADD PRIMARY KEY (column name);

Question 42

How do you remove a Primary key from a table?

Answer 42

ALTER TABLE table_name

DROP PRIMARY KEY;

Question 43

How do you select all data from a data?

Answer 43

SELECT * FROM table_name;

Question 44

How do you query data from one or more columns of a table?

Answer 44

SELECT
column1, column2, …
FROM
table_name;

Question 45

How do you remove duplicate rows from the result of a query?

Answer 45

SELECT
DISTINCT (column)
FROM
table_name;

Question 46

How do you query data with a filter using a WHERE clause?

Answer 46

SELECT select_list
FROM table_name
WHERE condition;

Question 47

What is the difference between a relational and non-relational database?

Answer 47

A relational database is a digital database based on the relational model of data. Data is stored in tables containing rows (which represents an entry) and columns (which stores and sorts a specific type of information).

Relation DB have fast query times, are not as scalable. Relation DB do better when you know how the data will be queried.

A non-relational database (or NoSQL) is a database that provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular references used in relational databases. Non-relation al there is no specific relationship between them. They do not have primary keys. Queries are not based on keys and relations to retrieve data, it’s more about having an idea of data you are looking for.

Non-relational DB perform better when you do not know how your data will be queried.