Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Assessment Review > Database/SQL > Flashcards

Database/SQL Flashcards

1
Q

A _________ is a collection of information that is organized so that it can be easily accessed, managed and updated.

A

database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

A _________ database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database.

A

relational

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Often, data in a ________ database is organized into tables.

A

relational

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called ______.

A

records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

_______ are labeled with a descriptive name (say, age for example) and have a specific _____ _____.

A

Columns are labeled with a descriptive name (say, age for example) and have a specific data type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

A ______ _______ ______ ______ (_____) is a program that allows you to create, update, and administer a relational database

A

relational database management system (RDBMS)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What does SQL stand for?

A

Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

___ (________ _____ ________) is a programming language used to communicate with data stored in a relational database management system

A

SQL (Structured Query Language)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

SQLite is a relational database management system

True or False

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

_____ contains a minimal set of SQL commands (which are the same across all RDBMSs). Other RDBMSs may use other variants.

A

SQLight

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Why do we need SQL?

1.

2.

3.

4.

5.

6.

7.

A
  • Allows users to access data in the relational database management systems.
  • Allows users to describe the data.
  • Allows users to define the data in a database and manipulate that data.
  • Allows to embed within other languages using SQL modules, libraries & pre-compilers.
  • Allows users to create and drop databases and tables.
  • Allows users to create view, stored procedure, functions in a database.
  • Allows users to set permissions on tables, procedures and views.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Describe a simple SQL Architecture

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

A _____ ___ is a field in a table which uniquely identifies each row/record in a database table.

A

primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Primary keys must contain ______ ______.

A

Unique Values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

A primary key column can have NULL values.

True or False

A

False

A primary key cannot have NULL values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

A table can have only one primary key

True or False

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

A primary key can only consist of a single field.

True or False

A

False

A primary key may consist of a single or multiple keys.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

When multiple fields are used as a primary key, they are called a ________ ___.

A

composite key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

If a table has a primary key defined on any field(s), then you can have two records having the same value of that field(s).

True or False

A

False

If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

20
Q

Generally, the primary key is created while creating the database and the table.

True or False

A

True

21
Q

The primary key can’t be created after the creation of the table as shown below.

True or False

A

False

The primary key can also be created after the creation of the table as shown below.

22
Q

Set a primary key on this column ID

CREATE TABLE CUSTOMERS(

ID INT NOT NULL, // To set a primary key on this column ID

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

A

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

23
Q

A _____ ___ is a key used to link two tables together.

A

foreign key

24
Q

A foriegn key is sometimes called a _______ ___

A

referencing key

25
Q

A Foreign Key is a column or a combination of columns whose values match a ______ ____ in a different table.

A

Primary Key

26
Q

The relationship between 2 tables matches the ______ ___ in one of the tables with a ______ ___ in the second table.

A

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

27
Q

Consider the following two tables, “Customers” and “Orders”. Once the tables have been created, what syntax could create the foreign key?

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2),

PRIMARY KEY (ID)

);

CREATE TABLE ORDERS (

ID. INT NOT NULL,

DATE DATETIME,

CUSTOMER_ID INT references CUSTOMERS(ID),

AMOUNT double,

PRIMARY KEY. (ID)

);

A

ALTER TABLE ORDERS

ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

28
Q

What does DDL mean?

A

Data Definition Language

29
Q

What are the 3 main commands of DDL?

A

CREATE, ALTER, DROP

CREATE - is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

ALTER - is used to alter the structure of the database.

DROP - used to delete objects from the database

TRUNCATE - is used to remove all records from a table, including all spaces allocated for the records are removed.

COMMENT –is used to add comments to the data dictionary.

RENAME –is used to rename an object existing in the database.

30
Q

What does DML Mean

A

Data Manipulation Language​

SELECT - is used to retrieve data from the a database.

INSERT - is used to insert data into a table.

UPDATE - is used to update existing data within a table.

DELETE - is used to delete records from a database table.

31
Q

What are the commands of DQL

A

Select - Insert - Update - Delete

SELECT - is used to retrieve data from the a database.

INSERT - is used to insert data into a table.

UPDATE - is used to update existing data within a table.

DELETE - is used to delete records from a database table.

32
Q

What does DCL mean?

A

Data Control Language

33
Q

What are the commands of DCL?

A

GRANT - REVOKE

GRANT - gives user’s access privileges to database.

REVOKE - withdraw user’s access privileges given by using the GRANT command.

34
Q

What does TCL mean?

A

Transaction Control Language

35
Q

What are the 3 TCL main statements/commands?

A

SAVEPOINT, ROLLBACK, COMMIT

COMMIT – commits a Transaction.

ROLLBACK – rollbacks a transaction in case of any error occurs.

SAVEPOINT –sets a savepoint within a transaction.

36
Q

The SQL ______ statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called __________.

A

The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

ex.

SELECT column1, column2, columnN FROM table_name;

37
Q

What syntax would you use to fetch all the fields available in the field?

A

SELECT * FROM table_name;

38
Q

Consider the following “Customers” table. Select only the ID, NAME and SALARY columns.

ID | NAME | AGE | ADDRESS | SALARY |

+—-+———-+—–+———–+———-+

+—-+———-+—–+———–+———-+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+—-+———-+—–+———–+———-+

A

SELECT ID, NAME, SALARY FROM CUSTOMERS;

39
Q

The SQL ______ clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table.

A

WHERE

40
Q

You should use the _____ clause to filter the records and fetching only the necessary records.

A

WHERE

41
Q

The WHERE clause is only used in the SELECT statement.

True or False

A

FALSE

The WHERE clause can be used in the SELECT, UPDATE & DELETE statement.

42
Q

Using the “Customers” table retrieve all the names of customers who’s age is greater than 20.

+—-+———-+—–+———–+———-+

+—-+———-+—–+———–+———-+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+—-+———-+—–+———–+———-+

ID | NAME | AGE | ADDRESS | SALARY |

A

SELECT CUSTOMERS.AGE from CUSTOMERS where AGE > 20;

NOTE:Can use DB name as prefix before column name.

43
Q

The SQL ___________ Statement is used to add new rows of data to a table in the database.

A

INSERT INTO

44
Q

Use an INSERT INTO statement to add data into each column of the following table.

+—-+———-+—–+———–+———-+

+—-+———-+—–+———–+———-+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+—-+———-+—–+———–+———-+

ID | NAME | AGE | ADDRESS | SALARY |

A

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, ‘Ramesh’, 32, ‘Ahmedabad’, 2000.00 );

45
Q

The SQL ____ clause is used to combine records from two or more tables in a database. A ____ is a means for combining fields from two tables by using values common to each.

A

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

46
Q

Perform innner join these two tables - Join the AMOUNT column from ORDERS table to the to the ID, NAME & AGE of the CUSTOMERS table by the CUSTOMER_ID and ID in each table

Customers Table

+—-+———-+—–+———–+———-+

+—-+———-+—–+———–+———-+

| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |

| 2 | Khilan | 25 | Delhi | 1500.00 |

| 3 | kaushik | 23 | Kota | 2000.00 |

| 4 | Chaitali | 25 | Mumbai | 6500.00 |

| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.00 |

| 7 | Muffy | 24 | Indore | 10000.00 |

+—-+———-+—–+———–+———-+

ORDERS Table

+—–+———————+————-+——–+

|OID | DATE | CUSTOMER_ID | AMOUNT |

+—–+———————+————-+——–+

| 102 | 2009-10-08 00:00:00 | 3 | 3000 |

| 100 | 2009-10-08 00:00:00 | 3 | 1500 |

| 101 | 2009-11-20 00:00:00 | 2 | 1560 |

| 103 | 2008-05-20 00:00:00 | 4 | 2060 |

+—–+———————+————-+——–+

ID | NAME | AGE | ADDRESS | SALARY |

A

SELECT Customers.ID, Customers.NAME, Customers.AGE, Orders.AMOUNT

FROM CUSTOMERS

INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

47
Q

What are the different types of Joins?

A

INNER JOIN - clause allows you to query data from two or more related tables. A comparison operator is required.

LEFT JOIN - It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.

RIGHT JOIN - clause starts selecting data from the right table and matching with the rows from the left table. The RIGHT JOIN returns a result set that includes all rows in the right table, whether or not they have matching rows from the left table.

FULL JOIN - returns a result set that includes rows from both left and right tables. When no matching rows exist for the row in the left table, the columns of the right table will have nulls. Similarly, when no matching rows exist for the row in the right table, the column of the left table will have nulls.

Assessment Review (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions