Postgresql

Question 1

PostgreSQL

Answer 1

is a database management system that uses SQL to interact with the database

Question 2

SQL

Answer 2

Standard Structures Query Language

Question 3

Types of data bases

Answer 3

Relational and non relational

Question 4

Types of relational databases

Answer 4

1. One-to-one relationship
2. One-to-Many relationship
3. Many-to-Many relationship

Question 5

Main command types in SQL

Answer 5

1. DDL- data definition language
2. DCL- data control language
3. DML- data manipulation language

Question 6

DDL commands

Answer 6

Data definition language
- create - create a database, table, temporary user, user
- drop - remove all the data and the structure of column, table and user
- alter - making changes remove, update
- truncate - remove all data in a table

Question 7

DCL commands

Answer 7

Data control language
Grant - give privileges
Revoke - remove privileges

Question 8

DML commands

Answer 8

Data manipulation language
- Insert - data in a table
- update - any data in a table
- delete - either while data in data base or certain data from table
- select - retrieve data from table

Question 9

Inner join

Answer 9

Retuns rows when there is a match in both tables

Question 10

Full join

Answer 10

Returns all rows when there is a match in one of the tables

Question 11

Left join

Answer 11

Returns all rows from the left table, and the matched rows from the right table

Question 12

Right join

Answer 12

Returns all rows from the right table and the matched rows from the left table

Question 13

What does truncate do

Answer 13

Removes all of the values inside of a table from the database

Question 14

What does drop do

Answer 14

Remove both the content and the structure of the table, database or user

Question 15

What is a constrain

Answer 15

Is a rule applied to columns or table in a database to maintain the integrity and accuracy of the data

Question 16

Types of constraints

Answer 16

Primary key, foreign key, unique, check, not null, default

Question 17

Data types

Answer 17

• numeric type: int, float, serial
• character type: text, char(50), varchar(50)
• data/time type
• Boolean type
• json

Question 18

What is alter used for

Answer 18

• add column
• rename column
• remove column
• add constraint
• remove constraint

Question 19

Distinct formula

Answer 19

Select distinct attributename from tablename;

Question 20

Limit formula

Answer 20

Select * from tablename LIMIT number;

Question 21

Aggregate functions formula

Answer 21

Select aggregate(attributename) from tablename;

AVG(), COUNT(), MIN(), MAX(), and SUM().

Question 22

Ascending, descending formula

Answer 22

Select * from tablename ORDER BY attributename ASC/DESC;

Question 23

Comparison operators formula

Answer 23

Select * from tablename WHERE attribute operator value;

Question 24

Arithmetic operators formula

Answer 24

Select attributename operator value from tablename;

Question 25

Why data validation is important

Answer 25

To ensure that data stored in a database is accurate, consistent and meets the specified standards and business rules.

Question 26

Formula to find duplicates

Answer 26

Select name, count()
from tablename
group by name
having count() >1;

Question 27

Formula of joins

Answer 27

Select attribitenames (or*)
from table1
name join table2
on table1.column = table2.column;

Question 28

Difference between char, varchar and text data types

Answer 28

• char - is fixed length character string
• varchar - is variable length character string
• text - is large variable length character string