Brainscape's Knowledge GenomeTM

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


See full index

SQL > Intro To Databases (COURSERA. META. Backend Development Certification) > Flashcards

Intro To Databases (COURSERA. META. Backend Development Certification) Flashcards

Revise SQL

1
Q

Name three typical use cases for databases.

A

Banks (customer and transaction data), hospitals (patient and staff data), and online stores (user profiles and shopping history).

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

What has led to the revolution in database technology?

A

The rise of Big Data and the Internet of Things (IoT), which generate vast amounts of diverse data.

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

List four key actions a database typically performs.

A
  1. Storing data,
  2. Forming relationships
  3. Filtering data
  4. Searching data.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What practical project will you undertake during this course?

A

Building a fully operational database and setting up software for local and remote database management.

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

According to Daniel, what is critical for a database engineer?

A

Creating an effective data layer for quick and accurate user responses, and influencing other aspects like user interfaces and APIs.

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

What soft skills are emphasized by Daniel for database engineers?

A

Communication and organization, especially explaining technical work to team members and end-users.

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

What advice does Daniel give about database development?

A

Avoid overcomplicating solutions, focus on current data needs, iterate frequently, and emphasize documentation.

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

How should you relate technical concepts to real-life scenarios?

A

Use relatable examples, like a recipe book, to understand and apply database concepts.

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

What additional task is important alongside coding according to Daniel?

A

Writing documentation, status updates, and enhancing project documentation.

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

What are the prerequisites for the Introduction to Databases course?

A

No previous database or coding experience required, but eagerness to start coding is essential.

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

What does CRUD stand for?

A

Create, Read, Update, Delete.

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

What is database normalization?

A

The process of organizing data to reduce redundancy and improve data integrity.

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

What is data?

A

Data are facts and figures about anything, such as name, age, email for a person, or order number, description, and quantity for a purchase.

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

What is a database?

A

A database is electronic storage where data is organized systematically, making it manageable, efficient, and secure.

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

What are entities in a database?

A

Entities are elements like employees, customers, products (physical) or orders, invoices (conceptual), stored in a table-like format.

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

What do attributes and rows represent in a database table?

A

Attributes are the columns representing features of the entity, and rows are the instances of the entity.

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

What are the different types of databases mentioned?

A
  1. Relational Databases: Use tables and relations.
  2. Object-Oriented Databases: Store data as objects.
  3. Graph Databases: Use nodes and edges to represent data and relationships.
  4. Document Databases: Use JSON objects organized into collections.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How can databases be stored?

A

On-Premises: Hosted on a dedicated machine within an organization.

Cloud Databases: Hosted on the cloud, accessed via the Internet, providing lower-cost options for data management.

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

What are the key takeaways about databases?

A
  1. Understand what a database is and how it functions.
  2. Identify real-world uses of databases.
  3. Understand how data is systematically organized within a database.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Why are data relationships important in a database?

A

Data must be related to be processed into meaningful information.

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

What is an example of a task that requires understanding data relationships in a database?

A

Retrieving customer details from one table and finding the corresponding order from another table.

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

What fields are typically found in a customer table?

A

Customer ID, FirstName, LastName, Email.

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

What is a primary key?

A

A unique field in a table that identifies each record (e.g., Customer ID).

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

What fields are typically found in an order table?

A

Order ID (primary key), Customer ID (foreign key).

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

What is a foreign key?

A

A field in one table that connects to the primary key in another table (e.g., Customer ID in the order table).

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

What are the purposes of using charts for data presentation?

A

To help people understand data better and to illustrate data relationships visually.

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

What does a bar chart represent?

A

Categorical data with rectangular bars, where the heights of the bars are proportional to the values they represent.

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

What insight does the bookshop’s bar chart from 2018 to 2022 provide?

A

2018 had the highest sales, while 2022 had the lowest sales.

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

How does a bubble chart compare different values?

A

By using bubbles of different sizes, with larger bubbles representing larger values.

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

What population sizes are represented by the largest bubbles in the 2015 bubble chart example?

A

China (1.4 billion) and India (1.3 billion).

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

What does a line chart show?

A

Trends over time by connecting data points with straight line segments.

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

What trend does the gold price line chart depict?

A

Changes in gold prices over a month, highlighting positive and negative changes.

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

How does a pie chart display data?

A

By showing how various data points make up a whole (100%) with each slice representing a percentage.

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

What percentage of students prefer soccer in the sports pie chart example?

A

50%

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

What additional types of charts are mentioned for different purposes?

A

Area charts, dual axis charts, Gantt charts, heat maps, and scatter plots.

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

What factors should be considered when choosing a chart for data presentation?

A

The target audience, the type and amount of data, the message, and the goal.

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

What is the best use of a line chart?

A

Identifying trends and predicting future data.

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

What is a simple and effective use of a pie chart?

A

Showing how various parts create a whole.

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

What are the chronological stages of database development?

A
  1. 1970s-1990s: Flat files, hierarchical, and network databases.
  2. 1980s-present: Relational databases.
  3. 1990s-present: Object-oriented, object-relational, and web-enabled databases.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What is a flat file database?

A

A type of database that stores data in a single file or table, typically as text files.

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

How is data organized in a hierarchical database?

A

Data is stored hierarchically, representing one-to-many relationships.

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

How do network databases differ from hierarchical databases?

A

Network databases allow multiple parent and child relationships, representing many-to-many relationships.

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

What key concepts define a relational database?

A

Storing data in tables with rows and columns, using primary keys for unique IDs, and foreign keys to establish relationships between tables.

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

What is an object-oriented database?

A

A database that represents data as objects, aligning with object-oriented programming languages.

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

What are the main types of NoSQL databases?

A
  1. Document Databases
  2. Key-Value Databases
  3. Wide-Column Databases
  4. Graph Databases
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

Why are NoSQL databases preferred for handling unstructured data?

A

Because of their higher scalability, distributed architecture, lower costs, flexible schema, and ability to process unstructured and semi-structured data.

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

What advantages do NoSQL databases offer?

A
  • Higher scalability
  • Distributed
  • Lower costs
  • Flexible schema
  • Can process unstructured and semi-structured data
  • No complex relationships
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

What is SQL?

A

SQL (Structured Query Language) is the standard language used to interact with all databases, especially relational databases.

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

What are CRUD operations in SQL?

A

Create, Read, Update, and Delete operations.

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

Name some examples of relational databases that use SQL.

A

MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

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

What is the role of a Database Management System (DBMS) in SQL?

A

A DBMS interprets and executes SQL instructions for the underlying database.

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

What does the CREATE command do in SQL?

A

It is used to create database objects like tables (part of DDL).

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

What is the purpose of the ALTER command in SQL?

A

It modifies the structure of database objects (part of DDL).

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

What does the DROP command do in SQL?

A

It removes database objects (part of DDL).

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

What does the INSERT command do in SQL?

A

It adds data to tables (part of DML).

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

How do you modify existing data in SQL?

A

Using the UPDATE command (part of DML).

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

What command is used to remove data from tables in SQL?

A

The DELETE command (part of DML).

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

What is the SELECT command used for in SQL?

A

To retrieve data from one or multiple tables (part of DQL).

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

What are the GRANT and REVOKE commands used for in SQL?

A

GRANT gives users access privileges to data, and REVOKE removes those privileges (part of DCL).

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

What is DDL in SQL?

A

Data Definition Language, used to define the structure of a database.

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

What is DML in SQL?

A

Data Manipulation Language, used to manipulate data within database objects.

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

What is DQL in SQL?

A

Data Query Language, used to read or retrieve data from the database.

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

What is DCL in SQL?

A

Data Control Language, used to control access to data within the database.

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

Give an example scenario where CRUD operations are used.

A

Creating a database for a college, inserting data, modifying data, and managing access.

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

How does SQL act as an interface between the database and its users?

A

By providing commands and sublanguages to manage and interact with the database effectively.

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

What are the advantages of SQL?

A
  1. User-friendly
  2. Interactive
  3. Standard language
  4. Portable
  5. Comprehensive
  6. Efficient
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

Why is SQL considered user-friendly?

A

It requires minimal coding skills and uses a set of keywords, making it easy to perform CRUD operations.

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

What makes SQL interactive?

A

It allows developers to write complex queries quickly.

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

How is SQL a standard language?

A

It can be used with all relational databases like MySQL, PostgreSQL, and Oracle, and has extensive support and resources available.

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

Why is SQL considered portable?

A

SQL code runs on any hardware and operating system, behaving the same across different platforms.

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

What makes SQL comprehensive?

A

It covers all aspects of database management, including creation, manipulation, retrieval, and security.

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

What are the subsets of SQL?

A
  1. Data Definition Language (DDL)
  2. Data Manipulation Language (DML)
  3. Data Query Language (DQL)
  4. Data Control Language (DCL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

What commands are included in Data Definition Language (DDL)?

A
  1. CREATE: Creates database objects like tables.
  2. ALTER: Modifies existing database objects.
  3. DROP: Deletes database objects.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

What commands are included in Data Manipulation Language (DML)?

A
  1. INSERT: Adds data to tables.
  2. UPDATE: Modifies existing data.
  3. DELETE: Removes data from tables.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

What is the main command in Data Query Language (DQL)?

A

SELECT: Retrieves data from the database.`

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

What commands are included in Data Control Language (DCL)?

A
  1. GRANT: Gives users access privileges.
  2. REVOKE: Removes access privileges.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

How do you create a database using SQL?

A

CREATE DATABASE college;

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

How do you create a table using SQL?

A 
CREATE TABLE student (
    ID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DateOfBirth DATE
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

How do you insert data into a table using SQL?

A 
INSERT INTO student (ID, FirstName, LastName, DateOfBirth)
VALUES (1, 'John', 'Murphy', '2000-01-01');
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

How do you update data in a table using SQL?

A 
UPDATE student
SET DateOfBirth = '2000-02-01'
WHERE ID = 1;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

How do you delete data from a table using SQL?

A 
DELETE FROM student
WHERE ID = 3;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

How do you query data from a table using SQL?

A 
SELECT FirstName, LastName
FROM student
WHERE ID = 1;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

What is SQL used for?

A

SQL is used for managing and retrieving data in relational databases.

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

What are the four main categories of SQL commands?

A
  1. Data Definition Language (DDL)
  2. Data Query Language (DQL)
  3. Data Manipulation Language (DML)
  4. Data Control Language (DCL)
  5. Transaction Control Language (TCL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

What is the purpose of the CREATE command in DDL?

A

To create a database or tables.

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

What is the syntax for creating a table?

A 
CREATE TABLE table_name (
    column_name1 datatype(size), 
    column_name2 datatype(size), 
    column_name3 datatype(size)
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
87
Q

What is the purpose of the DROP command in DDL?

A

To delete a database or table.

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

What is the syntax for dropping a table?

A

DROP TABLE table_name;

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

What is the purpose of the ALTER command in DDL?

A

To change the structure of a table.

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

What is the syntax for adding a column to a table?

A

ALTER TABLE table_name ADD (column_name datatype(size));

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

What is the syntax for adding a primary key to a table?

A 
ALTER TABLE table_name ADD primary key (column_name);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
92
Q

What is the purpose of the TRUNCATE command in DDL?

A

To remove all records from a table without deleting the table.

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

What is the syntax for truncating a table?

A

TRUNCATE TABLE table_name;

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

What is the purpose of the COMMENT command in DDL?

A

To add comments to SQL statements.

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

What is the syntax for adding a comment in SQL?

A 
-- Retrieve all data from a table
SELECT * FROM table_name;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
96
Q

What is the purpose of the SELECT command in DQL?

A

To retrieve data from tables.

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

What is the syntax for selecting data from a table?

A

SELECT * FROM table_name;

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

What is the purpose of the INSERT command in DML?

A

To add records to a table.

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

What is the syntax for inserting data into a table?

A

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

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

What is the purpose of the UPDATE command in DML?

A

To modify data in a table.

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

What is the syntax for updating data in a table?

A

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

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

What is the purpose of the DELETE command in DML?

A

To delete data from a table.

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

What is the syntax for deleting data from a table?

A

DELETE FROM table_name WHERE condition;

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

What is the purpose of the GRANT command in DCL?

A

To provide user privileges.

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

What is the syntax for granting privileges?

A

GRANT privilege_name ON object TO user;

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

What is the purpose of the REVOKE command in DCL?

A

To remove user privileges.

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

What is the syntax for revoking privileges?

A

REVOKE privilege_name ON object FROM user;

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

What is the purpose of the COMMIT command in TCL?

A

To save all changes made in the current transaction.

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

What is the syntax for committing changes?

A

COMMIT;

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

What is the purpose of the ROLLBACK command in TCL?

A

To restore the database to the last committed state.

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

What is the syntax for rolling back changes?

A

ROLLBACK;

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

What is a database table?

A

A database table stores and organizes data logically using rows and columns.

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

What are the components of a database table?

A

Rows (Records/Tuples), Columns (Fields/Attributes), and Cells.

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

What do rows in a database table represent?

A

Individual entries or records.

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

What do columns in a database table define?

A

The type of data stored.

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

What are binary data types used for in SQL?

A

To store binary data like images and files, such as BINARY, VARBINARY.

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

What is a primary key?

A

A column or combination of columns that uniquely identifies each row in a table.

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

What is a composite primary key?

A

A primary key that combines multiple columns to create a unique identifier.

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

What is a foreign key?

A

A column in one table that links to the primary key of another table.

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

What are domain constraints?

A

Rules that define valid values for a column.

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

What are referential integrity constraints?

A

Ensure foreign key values exist in the referenced table.

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

Give an example of a primary key in a Student Table.

A

student_id

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

Give an example of a foreign key relationship.

A

student_id in the Department Table referencing the Student Table.

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

What is a table (entity) in a database?

A

A table stores data in rows and columns, representing a type of entity.

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

What are binary data types used for in SQL?

A

To store binary data like images and files, such as BINARY, VARBINARY.

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

What is an Entity Relationship Diagram (ERD)?

A

A visual representation of how entities relate to each other in a database.

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

What is cardinality of relationships?

A

Defines how entities are related: One-to-One, One-to-Many, Many-to-Many.

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

How are relationships established in the physical database structure?

A

Using foreign keys.

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

What does the logical structure of a database include?

A

The logical structure includes entities, attributes, their names, and data types, represented in an ERD.

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

What is the purpose of assigning data types to columns in a database table?

A

To keep the data consistent and define what type of data can be stored in each column.

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

What is the relational database model based on?

A

Tables (entities) and relations (connections between tables).

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

What is a key attribute?

A

A value used to uniquely identify a record in a table.

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

Give an example of a key attribute.

A

staff_id in a staff table.

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

What is a candidate key?

A

Any attribute that contains a unique value in each row.

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

Give examples of candidate keys.

A

staff_id and contact_number in a staff table.

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

Give an example of a composite key.

A

Combination of staff_name and staff_title.

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

What is a primary key?

A

The main key attribute used to uniquely identify records.

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

Give an example of a primary key.

A

staff_id in a staff table.

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

What is an alternate key (secondary key)?

A

A candidate key that was not selected as the primary key.

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

Give an example of an alternate key.

A

contact_number in a staff table.

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

What is a foreign key?

A

An attribute that references a unique key in another table.

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

What is a database?

A

An organized collection of data.

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

Give examples of real-world database usage.

A

Banking systems, online stores.

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

How is data organized in a database?

A

Data is organized into tables with related data.

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

What is SQL (Structured Query Language)?

A

A language used to interact with and manage databases.

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

What are the advantages of SQL?

A
  1. Low entry level
  2. Wide range of applications
  3. Portability across operating systems
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
147
Q

What are the main subsets of SQL?

A
  1. DDL (Data Definition Language)
  2. DML (Data Manipulation Language)
  3. DQL (Data Query Language)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
148
Q

What are the main SQL commands?

A
  1. CREATE, ALTER, DROP (DDL)
  2. INSERT, UPDATE, DELETE (DML)
  3. SELECT (DQL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
149
Q

What is the purpose of data types in a database table?

A

To define the kind of data stored (e.g., INT, VARCHAR, DATE).

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

What did you learn about databases in this module?

A

Basics of databases, SQL, database structure, how data is stored, and methods for interacting with databases.

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

What is the purpose of data types in a database?

A

To define what kind of data is accepted by each field in a table and ensure columns accept the correct type of data.

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

What are the most common data types used in databases?

A

Numeric, string, and date and time data types.

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

What are numeric data types?

A

Data types that allow a column to store data as numbers in the database.

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

What is the integer data type used for?

A

For storing whole numbers.

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

What is the decimal data type used for?

A

For storing numbers with fractional values.

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

Give an example of a column that uses the integer data type.

A

Product quantity column.

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

Give an example of a column that uses the decimal data type.

A

Total price column.

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

What is TINYINT used for in a MySQL Database Management System?

A

For storing very small integer number values, with a maximum value of 255.

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

What is INT used for in a MySQL Database Management System?

A

For storing large integer number values, with a maximum value of over four billion.

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

What are string data types used for?

A

To define columns that accept both numeric and text characters.

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

What does CHAR stand for and what is it used for?

A

CHAR stands for character and is used to hold characters of a fixed length.

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

What does VARCHAR stand for and what is it used for?

A

VARCHAR stands for variable character and is used to hold characters of a variable length.

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

Give an example of a column that uses the CHAR data type.

A

Username column defined as CHAR(50).

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

Give an example of a column that uses the VARCHAR data type.

A

Student name column defined as VARCHAR(50).

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

What is TINYTEXT used for?

A

For defining columns that require less than 255 characters, like short paragraphs.

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

What is TEXT used for?

A

For defining columns that require less than 65,000 characters, like an article.

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

What is MEDIUMTEXT used for?

A

For defining columns that require up to 16.7 million characters, like the text of a book.

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

What is LONGTEXT used for?

A

For defining columns that require up to four gigabytes of text data.

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

What is the purpose of database constraints?

A

Constraints limit the type of data that can be stored in a table to ensure accuracy and reliability.

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

What happens if a data operation violates a constraint?

A

The operation is aborted.

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

What are column-level constraints?

A

Rules that apply to specific columns in a table.

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

What are table-level constraints?

A

Rules that apply to the entire table.

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

Give an example of a table-level constraint.

A

Foreign key constraints prevent actions that destroy links between tables.

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

What is the purpose of the NOT NULL constraint?

A

Ensures that data fields are always completed and never left blank.

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

Provide an example of a NOT NULL constraint in an SQL statement.

A 
CREATE TABLE customer (
    customer_id INT NOT NULL,
    customer_name VARCHAR NOT NULL
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
176
Q

What is the purpose of the DEFAULT constraint?

A

Sets a default value for a column if no value is specified.

177
Q

Provide an example of a DEFAULT constraint in an SQL statement.

A 
CREATE TABLE player (
    player_name VARCHAR NOT NULL,
    city VARCHAR DEFAULT 'Barcelona'
);
178
Q

Why are NOT NULL constraints important?

A

They prevent empty value fields and ensure fields are always filled.

179
Q

Why are DEFAULT constraints important?

A

They automatically fill a column with a default value if no data is provided.

180
Q

How do constraints ensure data integrity?

A

By validating data entry and preventing incorrect data.

181
Q

How do constraints automate data entry?

A

By reducing the need to repeatedly enter the same values.

182
Q

How do constraints enforce rules in a database?

A

By applying specific rules to columns or tables to maintain consistency and reliability.

183
Q

What is the purpose of creating a database?

A

To store and organize data relevant to the business needs, such as book titles, authors, customers, and sales for an online bookstore.

184
Q

What is the SQL syntax to create a database?

A

CREATE DATABASE database_name;

185
Q

What are the requirements for a database name?

A

The name must be meaningful, relevant, unique, and within 63 characters.

186
Q

How do you create a database named “bookstore2_db”?

A

CREATE DATABASE bookstore2_db;

187
Q

What is the purpose of dropping a database?

A

To remove an existing database from the system.

188
Q

What is the SQL syntax to drop a database?

A

DROP DATABASE database_name;

189
Q

How do you drop a database named “bookstore_db”?

A

DROP DATABASE bookstore_db;

190
Q

What is the purpose of creating a table in a database?

A

To hold and organize data in a structured format within a database.

191
Q

What must exist before you can create tables in a database?

A

A database must already be created on the server.

192
Q

What is the SQL syntax to create a table?

A 
CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type
);
193
Q

How do you create a table named “customers” with columns “customer_name” and “phone_number”?

A 
CREATE TABLE customers (
    customer_name VARCHAR(255),
    phone_number INT
);
194
Q

What data type should be used for a column holding text data?

A

VARCHAR

195
Q

What data type should be used for a column holding whole numbers?

A

INT

196
Q

What is the purpose of altering tables in a database?

A

To restructure tables by adding, removing, or modifying columns and their attributes.

197
Q

What are the SQL keywords used to inform the database of changes to a table?

A

ALTER TABLE

198
Q

How do you add a new column to a table using SQL?

A

ALTER TABLE table_name ADD (column_name data_type);

199
Q

Provide an example of an SQL statement to add columns age, country, and nationality to the students table.

A

ALTER TABLE students ADD (age INT, country VARCHAR(50), nationality VARCHAR(255));

200
Q

How do you remove a column from a table using SQL?

A

ALTER TABLE table_name DROP COLUMN column_name;

201
Q

Provide an example of an SQL statement to remove the nationality column from the students table.

A

ALTER TABLE students DROP COLUMN nationality;

202
Q

How do you modify a column in a table using SQL?

A

ALTER TABLE table_name MODIFY column_name new_data_type;

203
Q

Provide an example of an SQL statement to change the country column to hold 100 characters instead of 50.

A

ALTER TABLE students MODIFY country VARCHAR(100);

204
Q

What is the purpose of the INSERT INTO clause in SQL?

A

To add new rows of data to a table.

205
Q

Provide the basic syntax for inserting a single row of data into a table.

A

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

206
Q

Provide an example of an SQL statement to insert player data for Yuval into the players table.

A

INSERT INTO players (ID, Name, Age, Start_date) VALUES (1, 'Yuval', 25, '2020-10-15');

207
Q

How do you insert multiple rows of data into a table using SQL?

A 
INSERT INTO table_name (column1, column2, column3) VALUES 
(value1, value2, value3),
(value4, value5, value6);
208
Q

Provide an example of an SQL statement to insert player data for Mark and Karl into the players table.

A 
INSERT INTO players (ID, Name, Age, Start_date) VALUES 
(2, 'Mark', 27, '2020-10-12'),
(3, 'Karl', 26, '2020-10-07');
209
Q

What is the purpose of the SELECT statement in SQL?

A

To retrieve data from a table.

210
Q

Provide the basic syntax for retrieving all data from a table using SQL.

A

SELECT * FROM table_name;

211
Q

Provide an example of an SQL statement to retrieve all data from the players table.

A

SELECT * FROM players;

212
Q

What is the main objective of learning the CREATE TABLE statement?

A

To develop a practical understanding of how the CREATE TABLE statement works and to use its syntax correctly.

213
Q

Why is it important to give meaningful names to your table and its columns/fields?

A

It helps in documentation and understanding the purpose of the table and columns.

214
Q

Why do data types vary between database systems?

A

Different database systems support different data types (e.g., NUMBER in Oracle vs. INT in MySQL).

215
Q

What should you do to ensure the correct data type usage in your database system?

A

Refer to the list of data types supported by the specific database system you are using.

216
Q

Why is VARCHAR preferred for storing text-based data?

A

Because it saves space, taking 1 byte per character plus 2 bytes for length.

217
Q

Provide an example of how much space VARCHAR takes for storing a name “Jen” with VARCHAR(100).

A

It takes 5 bytes (3 bytes for J, E, N, plus 2 bytes for length).

218
Q

What is the basic syntax for creating a table in SQL?

A 
CREATE TABLE table_name (
    column1_name data_type(length),
    column2_name data_type(length),
    ...
);
219
Q

Provide an example of a CREATE TABLE statement for a table named customers.

A 
CREATE TABLE customers (
    CustomerId INT,
    FirstName VARCHAR(40),
    LastName VARCHAR(20),
    Company VARCHAR(80),
    Address VARCHAR(70),
    City VARCHAR(40),
    State VARCHAR(40),
    Country VARCHAR(40),
    PostalCode VARCHAR(10),
    Phone VARCHAR(24),
    Fax VARCHAR(24),
    Email VARCHAR(60),
    SupportRapid INT
);
220
Q

What should follow the table name in the CREATE TABLE statement?

A

A pair of parentheses enclosing column definitions.

221
Q

What should be included within the parentheses of the CREATE TABLE statement?

A

Column names, their respective data types, and the size or length of data that can be stored.

222
Q

What is the purpose of the semicolon at the end of the CREATE TABLE statement?

A

To indicate the end of the SQL statement.

223
Q

What is the data type used for storing numeric values in the customers table example?

A

INT

224
Q

What data type and length are used for storing text-based data in the customers table example?

A

VARCHAR with varying lengths specified for each column.

225
Q

What is the purpose of the SQL SELECT statement?

A

To retrieve data from a table and perform actions such as math calculations, date and time queries, and concatenation functions.

226
Q

What is the basic syntax for a SQL SELECT statement?

A

SELECT column_name FROM table_name;

227
Q

Provide an example of a SQL SELECT statement to retrieve player names from a players table.

A

SELECT name FROM players;

228
Q

How do you retrieve data from multiple columns in a SQL SELECT statement?

A

SELECT column1, column2 FROM table_name;

229
Q

Provide an example of a SQL SELECT statement to retrieve player names and skill levels from a players table.

A

SELECT name, level FROM players;

230
Q

How do you retrieve all data from all columns in a table using a SQL SELECT statement?

A

SELECT * FROM table_name;

231
Q

Provide an example of a SQL SELECT statement to retrieve all data from a players table.

A

SELECT * FROM players;

232
Q

What is the purpose of the INSERT INTO SELECT statement?

A

To retrieve information from one or more tables and populate columns in another table.

233
Q

What is the basic syntax for an INSERT INTO SELECT statement?

A

INSERT INTO target_table (column_name) SELECT column_name FROM source_table;

234
Q

Provide an example of an INSERT INTO SELECT statement to populate the countryName column in a country table with data from the country column in a player table.

A

INSERT INTO country_table (countryName) SELECT country FROM player_table;

235
Q

What are the main keywords used in an INSERT INTO SELECT statement?

A

INSERT INTO, SELECT, FROM

236
Q

What should you do after writing an INSERT INTO SELECT statement?

A

Execute the command to insert data from the source table into the target table.

237
Q

How can you verify that data has been correctly inserted using an INSERT INTO SELECT statement?

A

Check the target table to ensure the appropriate column has been populated with the correct data.

238
Q

What is the purpose of the SQL UPDATE statement?

A

To update specific columns in a table for one or multiple records.

239
Q

What is the basic syntax for a SQL UPDATE statement?

A 
UPDATE table_name
SET column1 = 'new_value1', column2 = 'new_value2'
WHERE condition;
240
Q

Provide an example of an UPDATE statement to update the home address and contact number for the student with ID 3.

A 
UPDATE student_table
SET home_address = 'new_address', contact_number = 'new_number'
WHERE ID = 3;
241
Q

How do you update the college address for all engineering students?

A 
UPDATE student_table
SET college_address = 'Harper Building'
WHERE department = 'engineering';
242
Q

How do you update multiple columns with one UPDATE statement?

A 
UPDATE student_table
SET home_address = 'new_address', college_address = 'Harper Building'
WHERE department = 'engineering';
243
Q

What is the purpose of the SQL DELETE statement?

A

To remove specific records or all records from a table.

244
Q

What is the basic syntax for a SQL DELETE statement?

A 
DELETE FROM table_name
WHERE condition;
245
Q

Provide an example of a DELETE statement to remove the record of the student with the last name ‘Miller’.

A 
DELETE FROM student_table
WHERE last_name = 'Miller';
246
Q

How do you delete records of all engineering students?

A 
DELETE FROM student_table
WHERE department = 'engineering';
247
Q

How do you delete all records from a table using a DELETE statement?

A

DELETE FROM student_table;

248
Q

What is the key difference in the DELETE statement when removing multiple records vs. all records?

A

When removing multiple records, include a WHERE clause; when removing all records, omit the WHERE clause.

249
Q

What are operators in SQL?

A

Operators are specific words or characters that help perform different activities in a database, similar to operation keys on a calculator.

250
Q

What do arithmetic operators in SQL do?

A

Arithmetic operators perform calculations and return results, and are used with numerical data in SQL tables.

251
Q

List the five main arithmetic operators in SQL.

A
  1. Addition (+)
  2. Subtraction (-)
  3. Multiplication (*)
  4. Division (/)
  5. Modulus (%)
252
Q

How do you perform an addition operation in SQL?

A

SELECT column_name1 + column_name2 FROM table_name;

SELECT salary + allowance FROM employee;

253
Q

How do you use an addition operator in a WHERE clause?

A

SELECT * FROM employee WHERE salary + allowance = 25000;

Employees with IDs 1 and 4.

254
Q

How do you perform a subtraction operation in SQL?

A

SELECT column_name1 - column_name2 FROM table_name;

SELECT salary - tax FROM employee;

255
Q

How do you use a subtraction operator in a WHERE clause?

A

SELECT * FROM employee WHERE salary - tax = 50000;

256
Q

Why are arithmetic operators useful in SQL?

A

They are useful for performing mathematical operations on the data in tables while retrieving them by writing SQL SELECT queries.

257
Q

How can arithmetic operators be used in SQL clauses?

A

They can be used in the SELECT clause to retrieve data and in the WHERE clause to filter data based on specified conditions.

258
Q

Provide an example of using the modulus operator in SQL.

A

SELECT 100 % 10;

0 (since 100 divided by 10 equals 10 with no remainder).

259
Q

How do you write a SQL SELECT statement to double the tax amounts for each employee in a table?

A

SELECT tax * 2 FROM employee;

260
Q

Write the SQL SELECT statement to find employees who must pay an amount of tax equal to 4000 after doubling the current tax value.

A 
SELECT *  
FROM employee 
WHERE tax * 2 = 4000;
261
Q

What is the result of the following SQL query: SELECT tax * 2 FROM employee; if the tax values are 1000, 2000, 2000, 1000?

A 
tax * 2
2000
4000
4000
2000
262
Q

How do you find the allowance percentage each employee receives using SQL?

A

SELECT allowance / salary * 100 FROM employee;

263
Q

Write the SQL SELECT statement to find employees receiving an allowance of at least 5%.

A 
SELECT *  
FROM employee 
WHERE allowance / salary * 100 >= 5;
264
Q

What is the result of the SQL query SELECT allowance / salary * 100 FROM employee; if the salary and allowance values are (24000, 1000), (55000, 3000), (52000, 3000), and (24000, 1000)?

A 
allowance / salary * 100
4.1667
5.4545
5.7692
4.1667
265
Q

What is the purpose of using arithmetic operators in the WHERE clause of a SQL SELECT statement?

A

Arithmetic operators in the WHERE clause are used to filter out data based on specific arithmetic conditions applied to numerical columns.

266
Q

What is the result of the following SQL query: SELECT * FROM employee WHERE allowance / salary * 100 >= 5; if the table contains (employee_id, employee_name, salary, allowance, tax) values (1, ‘alex’, 24000, 1000, 1000), (2, ‘John’, 55000, 3000, 2000), (3, ‘James’, 52000, 3000, 2000), (4, ‘Sam’, 24000, 1000, 1000)?

A 
employee_id | employee_name | salary | allowance | tax
2           | John          | 55000  | 3000      | 2000
3           | James         | 52000  | 3000      | 2000
267
Q

What does the modulus operator (%) do in SQL?

A

It returns the remainder when the numerical values of one column are divided by the numerical values of another column.

268
Q

What is the syntax for using the modulus operator in SQL?

A

SELECT column_name1 % column_name2 FROM table_name;

269
Q

How can you check if the number of hours worked by each employee is even or odd using SQL?

A

SELECT hours % 2 FROM employee;

270
Q

What does a remainder of 0 indicate when using the modulus operator on the hours column?

A

It indicates that the number of hours worked is an even number.

271
Q

How do you filter out employees who worked an even number of hours using the modulus operator in SQL?

A

SELECT * FROM employee WHERE hours % 2 = 0;

272
Q

What is the output when the modulus operation returns 1 on the hours column?

A

It indicates that the number of hours worked is an odd number.

273
Q

What are the key arithmetic operators in SQL?

A

Addition (+), Subtraction (-), Multiplication (*), Division (/), and Modulus (%).

274
Q

How can arithmetic operators be used in SQL?

A

They can be used in the SELECT clause to perform mathematical operations on data and in the WHERE clause to filter data based on specific conditions.

275
Q

What is an example of using the addition operator in SQL?

A

SELECT salary + allowance FROM employee;

276
Q

How do you use the subtraction operator in SQL to find the remaining salary after tax deduction?

A

SELECT salary - tax FROM employee;

277
Q

What SQL operator is used to add a $500 bonus to each employee’s salary?

A

SELECT salary + 500 FROM employee;

278
Q

How do you deduct $500 from each employee’s salary using SQL?

A

SELECT salary - 500 FROM employee;

279
Q

What is the SQL query to double the current annual salary of each employee?

A

SELECT salary * 2 FROM employee;

280
Q

How can you determine the monthly salary of each employee using SQL?

A

SELECT salary / 12 FROM employee;

281
Q

How do you check if an employee’s ID is even or odd using SQL?

A

SELECT ID % 2 FROM employee;

282
Q

What does the SQL query SELECT salary + 500 FROM employee; do?

A

Adds a $500 bonus to each employee’s salary.

283
Q

What does the SQL query SELECT salary - 500 FROM employee; accomplish?

A

Deducts $500 from each employee’s salary.

284
Q

What is the purpose of the query SELECT salary * 2 FROM employee;?

A

To double each employee’s salary.

285
Q

What does the query SELECT salary / 12 FROM employee; calculate?

A

The monthly salary of each employee.

286
Q

What information does the query SELECT ID % 2 FROM employee; provide?

A

It determines if an employee ID is even (remainder 0) or odd.

287
Q

What result would you get from SELECT salary + 500 FROM employee; if an employee’s current salary is $3000?

A

$3500

288
Q

If an employee’s ID is 5, what would the query SELECT ID % 2 FROM employee; return?

A

1 (indicating an odd number)

289
Q

Using the query SELECT salary - 500 FROM employee;, what is the new salary if the original salary was $2000?

A

$1500

290
Q

In the query SELECT salary / 12 FROM employee;, if the annual salary is $36000, what is the result?

A

$3000 (monthly salary)

291
Q

What does a result of 0 from the query SELECT ID % 2 FROM employee; indicate about the employee’s ID?

A

The employee ID is even.

292
Q

Name the common SQL comparison operators.

A

Equal to (=), Less than (<), Greater than (>), Less than or equal to (<=), Greater than or equal to (>=), Not equal to (<> or !=).

293
Q

Write a SQL query to find employees earning exactly $18,000 per year.

A

SELECT * FROM employee WHERE salary = 18000;

294
Q

Which operator would you use to find employees earning less than $24,000 per year?

A

Less than (<) operator.

295
Q

Provide an example SQL query using the less than operator to find employees earning less than $24,000.

A

SELECT * FROM employee WHERE salary < 24000;

296
Q

How would you write a query to find employees earning $24,000 or less per year?

A

SELECT * FROM employee WHERE salary <= 24000;

297
Q

Write a SQL query to find employees earning $24,000 or more per year.

A

SELECT * FROM employee WHERE salary >= 24000;

298
Q

Which operator checks if values are not equal in SQL?

A

Not equal to (<> or !=).

299
Q

Provide a SQL query to find employees with a salary not equal to $24,000.

A

SELECT * FROM employee WHERE salary <> 24000;

300
Q

Explain the result of the following query: SELECT * FROM employee WHERE salary <= 24000;

A

It returns all employees earning $24,000 or less per year.

301
Q

What does the “=” operator do in SQL?

A

Checks for equality

302
Q

What do the “<>” or “!=” operators do in SQL?

A

Check for inequality.

303
Q

What does the “>” operator do in SQL?

A

Checks if something is greater than.

304
Q

What does the “>=” operator do in SQL?

A

Checks if something is greater than or equal to.

305
Q

What does the “<” operator do in SQL?

A

Checks if something is less than.

306
Q

What does the “<=” operator do in SQL?

A

Checks if something is less than or equal to.

307
Q

How would you write a SQL query to retrieve data for the employee with ID 1?

A

SELECT * FROM employee WHERE employee_id = 1;

308
Q

How would you write a SQL query to retrieve data for the employee named James?

A

SELECT * FROM employee WHERE employee_name = 'James';

309
Q

In SQL, what must you surround text values with when using the equality operator?

A

Single quotation marks.

310
Q

What would be the result of the query SELECT * FROM employee WHERE employee_id = 1; on the provided table?

A 
| employee_ID | employee_name | salary | hours | allowance | tax  |
|-------------|---------------|--------|-------|-----------|------|
| 1           | Alex          | 24000  | 10    | 1000      | 1000 |
311
Q

What is the purpose of using comparison operators in the WHERE clause of a SELECT statement?

A

To filter out records from a table based on specific conditions.

312
Q

How do you write a SQL query to find employees with an allowance less than 2500?

A 
SELECT * 
FROM employee 
WHERE allowance < 2500;
313
Q

How do you write a SQL query to find employees who worked for less than or equal to 10 hours?

A 
SELECT * 
FROM employee 
WHERE hours <= 10;
314
Q

What is the purpose of the ORDER BY clause in SQL?

A

The ORDER BY clause is used to sort data in either ascending (ASC) or descending (DESC) order.

315
Q

Is the ORDER BY clause mandatory in a SELECT statement?

A

No, the ORDER BY clause is optional.

316
Q

What is the default sort order if neither ASC nor DESC is specified in the ORDER BY clause?

A

The default sort order is ascending (ASC).

317
Q

How do you write a basic ORDER BY clause to sort data by a single column?

A 
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];
318
Q

How do you write an ORDER BY clause to sort data by multiple columns?

A 
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
319
Q

How does the data type of a column affect sorting with the ORDER BY clause?

A

Numeric data types are sorted in numerical order, and text-based or string data types are sorted in alphabetical order.

320
Q

Write an SQL statement to sort students by nationality in ascending order.

A 
SELECT ID, first_name, last_name, nationality
FROM student_table
ORDER BY nationality ASC;
321
Q

Write an SQL statement to sort students by nationality in descending order.

A 
SELECT ID, first_name, last_name, nationality
FROM student_table
ORDER BY nationality DESC;
322
Q

Write an SQL statement to sort students by nationality in ascending order and date of birth in descending order.

A 
SELECT ID, first_name, last_name, date_of_birth, nationality
FROM student_table
ORDER BY nationality ASC, date_of_birth DESC;
323
Q

How do you select all columns in a table for sorting using the ORDER BY clause?

A 
SELECT *
FROM table_name
ORDER BY column_name [ASC|DESC];
324
Q

What is the basic syntax for using the ORDER BY clause in SQL?

A 
SELECT *  
FROM Employee 
ORDER BY <column_name> [ASC|DESC];
325
Q

What happens if you do not specify ASC or DESC in the ORDER BY clause?

A

The data is sorted in ascending order by default.

326
Q

How does the ORDER BY clause handle different data types?

A

It sorts numeric columns in numerical order, text columns in alphabetical order, and date columns in chronological order.

327
Q

Write an SQL statement to sort customer data by CustomerId in descending order.

A 
SELECT * 
FROM customers 
ORDER BY CustomerId DESC;
328
Q

How do you sort a text column, such as City, in ascending order?

A 
SELECT * 
FROM customers 
ORDER BY City;
329
Q

How do you sort a date column, such as InvoiceDate, in ascending order?

A 
SELECT * 
FROM invoices 
ORDER BY InvoiceDate;
330
Q

Write an SQL statement to sort data by City in descending order.

A 
SELECT * 
FROM customers 
ORDER BY City DESC;
331
Q

How do you sort data by multiple columns, for example by BillingCity in ascending order and InvoiceDate in descending order?

A 
SELECT * 
FROM invoices 
ORDER BY BillingCity ASC, InvoiceDate DESC;
332
Q

What is the result of the following SQL query?
~~~
SELECT *
FROM invoices
ORDER BY InvoiceDate DESC;
~~~

A

The data is sorted from the largest to smallest date, which is in descending order.

333
Q

If you want to sort the CustomerId column in ascending order, which SQL keyword can be omitted?

A

ASC can be omitted because ascending order is the default.

334
Q

What is the purpose of the WHERE clause in SQL?

A

The WHERE clause is used to filter and extract records from a database that meet specified conditions.

335
Q

Write the basic syntax of a SQL SELECT statement with a WHERE clause.

A 
SELECT column_names
FROM table_name
WHERE condition;
336
Q

How do you filter records where the column value is equal to a specified operand in SQL?

A

Use the equals operator (=) in the WHERE clause.
SELECT * FROM student_table WHERE student_id = 01;

337
Q

List other comparison operators that can be used in the WHERE clause.

A
  • Less than (<)
  • Greater than (>)
  • Less than or equal to (<=)
  • Greater than or equal to (>=)
  • Not equal to (<> or !=)
338
Q

How does the BETWEEN operator work in the WHERE clause?

A

The BETWEEN operator filters records within a specific numeric or date range.
SELECT * FROM Students WHERE DateOfBirth BETWEEN '2010-01-01' AND '2010-05-30';

339
Q

How does the LIKE operator work in the WHERE clause?

A

The LIKE operator filters records that match a specified pattern. % represents zero or more characters, _ represents a single character.
SELECT * FROM student_table WHERE faculty LIKE 'Sc%';

340
Q

How does the IN operator work in the WHERE clause?

A

The IN operator filters records where the column value matches any value in a specified list.
SELECT * FROM student_table WHERE country IN ('USA', 'UK');

341
Q

Write an example SQL query to retrieve details of students in the engineering faculty.

A

SELECT * FROM student_table WHERE faculty = 'engineering';

342
Q

Write an SQL query to retrieve students born between January 1, 2010, and May 30, 2010.

A

SELECT * FROM Students WHERE DateOfBirth BETWEEN '2010-01-01' AND '2010-05-30';

343
Q

Write an SQL query to retrieve students in the science faculty (starting with “Sc”).

A

SELECT * FROM student_table WHERE faculty LIKE 'Sc%';

344
Q

Write an SQL query to retrieve students from the USA and UK.

A

SELECT * FROM student_table WHERE country IN ('USA', 'UK');

345
Q

Can the WHERE clause be used in other SQL statements apart from SELECT?

A

Yes, the WHERE clause can also be used in UPDATE and DELETE statements to filter records that should be updated or deleted.

346
Q

What is the function of the ALL logical operator?

A

Used to compare a single value to all values in another set.

347
Q

What is the function of the AND logical operator?

A

Allows for the existence of multiple conditions in an SQL statement’s WHERE clause.

348
Q

What is the function of the ANY logical operator?

A

Used to compare a value to any applicable value in the list as per the condition.

349
Q

What is the function of the BETWEEN logical operator?

A

Used to search for values within a set of values, given the minimum value and the maximum value.

350
Q

What is the function of the EXISTS logical operator?

A

Used to search for the presence of a row in a specified table that meets a certain criterion.

351
Q

What is the function of the IN logical operator?

A

Used to compare a value to a list of literal values that have been specified.

352
Q

What is the function of the LIKE logical operator?

A

Used to compare a value to similar values using wildcard operators.

353
Q

What is the function of the NOT logical operator?

A

Reverses the meaning of the logical operator with which it is used.

354
Q

What is the function of the OR logical operator?

A

Used to combine multiple conditions in an SQL statement’s WHERE clause.

355
Q

What is the function of the IS NULL logical operator?

A

Used to compare a value with a NULL value.

356
Q

What is the function of the UNIQUE logical operator?

A

Searches every row of a specified table for uniqueness (no duplicates).

357
Q

How would you use the WHERE clause to fetch invoices with a total value greater than $2?

A 
SELECT *  
FROM invoices  
WHERE Total > 2;
358
Q

How can you combine multiple conditions in the WHERE clause using the AND operator?

A 
SELECT column1, column2, columnN  
FROM table_name 
WHERE [condition1] AND [condition2]...AND [conditionN];
359
Q

Provide an example of using the AND operator to fetch invoices with a total value greater than $2 and BillingCountry as the USA.

A 
SELECT *  
FROM invoices 
WHERE Total > 2 AND BillingCountry = 'USA';
360
Q

How can you combine multiple conditions in the WHERE clause using the OR operator?

A 
SELECT column1, column2, columnN  
FROM table_name 
WHERE [condition1] OR [condition2]...OR [conditionN];
361
Q

Provide an example of using the OR operator to fetch invoices where BillingCountry is either the USA or France.

A 
SELECT *  
FROM invoices 
WHERE BillingCountry = 'USA' OR BillingCountry = 'France';
362
Q

How can you use both AND and OR operators together in the WHERE clause to fetch invoices with a total value over $2 and BillingCountry as either the USA or France?

A

SELECT * FROM invoices WHERE Total > 2 AND (BillingCountry = 'USA' OR BillingCountry = 'France');

363
Q

What is the purpose of the SELECT DISTINCT clause?

A

The SELECT DISTINCT clause is used to retrieve unique values from a column or columns in a table, eliminating duplicate records from the result set.

364
Q

What is the basic syntax for using SELECT DISTINCT?

A

SELECT DISTINCT column_name FROM table_name;

365
Q

How does the SELECT DISTINCT clause work with a single column?

A

It removes duplicate entries and returns only unique values from the specified column.

366
Q

How can you retrieve unique combinations of values from multiple columns using SELECT DISTINCT?

A

SELECT DISTINCT column1, column2 FROM table_name;

367
Q

How does SELECT DISTINCT handle NULL values in columns?

A

SELECT DISTINCT treats NULL values as unique, so rows with NULL values will be included in the result as distinct entries.

368
Q

Provide an example of using SELECT DISTINCT on a single column.

A

SELECT DISTINCT country FROM students;

369
Q

Provide an example of using SELECT DISTINCT on multiple columns.

A

SELECT DISTINCT faculty, country FROM students;

370
Q

How can SELECT DISTINCT be used with SQL aggregate functions like COUNT?

A

SELECT COUNT(DISTINCT column_name) FROM table_name;

SELECT COUNT(DISTINCT country) FROM customers;

371
Q

What is an important point to remember about SELECT DISTINCT with multiple columns?

A

When multiple columns are specified, SELECT DISTINCT returns unique combinations of values for those columns.

372
Q

Provide an example query to get unique billing countries from an invoices table.

A

SELECT DISTINCT BillingCountry FROM invoices ORDER BY BillingCountry;

373
Q

Provide an example query to get unique combinations of billing countries and cities from an invoices table.

A

SELECT DISTINCT BillingCountry, BillingCity FROM invoices ORDER BY BillingCountry, BillingCity;

374
Q

What happens when SELECT DISTINCT is used on a column containing NULL values?

A

It includes NULL as a unique value, treating it as distinct from other values.

375
Q

What is the default order when using the SELECT DISTINCT clause?

A

The default order is ascending.

376
Q

Provide an example query to find unique combinations of BillingCountry and BillingCity including NULL values.

A

SELECT DISTINCT BillingCountry, BillingCity FROM invoices ORDER BY BillingCountry, BillingCity;

377
Q

What does the SELECT DISTINCT clause do?

A

Eliminates duplicate values from the result set.

378
Q

How do you use SELECT DISTINCT with a single column?

A

SELECT DISTINCT column_name FROM table_name;

379
Q

How do you use SELECT DISTINCT with multiple columns?

A

SELECT DISTINCT column1, column2 FROM table_name;

380
Q

Does SELECT DISTINCT consider NULL values as unique?

A

Yes, it treats any NULL values in the DISTINCT column(s) as unique.

381
Q

What are some key skills acquired from this module?

A

Using SQL arithmetic operators, applying comparison operators, sorting data with ORDER BY, filtering data with WHERE, and eliminating duplicates with SELECT DISTINCT.

382
Q

What is a database schema?

A

A blueprint of how data is organized and related in a database.

383
Q

How are schema and database terms used in MySQL?

A

In MySQL, schema and database are interchangeable terms.

384
Q

What does a SQL Server schema include?

A

A collection of components like tables, fields, datatypes, and keys.

385
Q

What is a conceptual or logical schema?

A

It describes the structure of the entire database for all users in terms of entities and their relationships.

386
Q

What does an internal or physical schema describe?

A

The physical storage of the database, including how data is stored on disk in the form of tables, columns, and records.

387
Q

What is an external or view schema?

A

A user-specific view of the database that shows only relevant data for that user.

388
Q

What are the three types of database schema?

A

Conceptual or logical schema, internal or physical schema, external or view schema.

389
Q

What is an example of a logical schema?

A

A diagram depicting entities like Employee and Department with their attributes and relationships.

390
Q

What is an example of an internal schema?

A

A detailed description of how the Employee table should physically store its data.

391
Q

How does an external schema benefit different users?

A

It provides different views of the database tailored to the specific needs of different users.

392
Q

What is the three-schema architecture?

A

A framework that includes conceptual, internal, and external levels of schema.

393
Q

What is data modeling in the context of databases?

A

The process of designing the database schema, which serves as the skeleton of the database without storing actual data.

394
Q

How do schemas provide security?

A

By granting permission to separate and protect database objects based on user access rights.

395
Q

What command is used to create the cart_order table with foreign keys?

A

CREATE TABLE cart_order ( orderID INT PRIMARY KEY, customerID INT, productID INT, quantity INT, order_date DATE, status VARCHAR(100), FOREIGN KEY (customerID) REFERENCES customer(customerID), FOREIGN KEY (productID) REFERENCES product(productID) );

396
Q

How would you create a composite primary key in SQL?

A

CREATE TABLE delivery ( customer_id INT, product_code INT, PRIMARY KEY (customer_id, product_code) );

397
Q

How would you define a foreign key in SQL?

A 
CREATE TABLE order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
398
Q

How would you define multiple foreign keys in SQL?

A

CREATE TABLE order ( order_id INT PRIMARY KEY, customer_id INT, product_id INT, FOREIGN KEY (customer_id) REFERENCES customer(customer_id), FOREIGN KEY (product_id) REFERENCES product(product_id) );

399
Q

How do you select the ‘automobile’ database in SQL?

A

USE automobile;

400
Q

Write the SQL syntax to create the vehicle table.

A

CREATE TABLE vehicle( vehicleID VARCHAR(10), ownerID VARCHAR(10), plateNumber VARCHAR(10), phoneNumber INT, PRIMARY KEY (vehicleID) );

401
Q

How do you display all tables in the ‘automobile’ database?

A

SHOW TABLES;

402
Q

How do you display the structure of the vehicle table in SQL?

A

SHOW COLUMNS FROM vehicle;

403
Q

How do you alter the vehicle table to add a foreign key linking to the owner table?

A

ALTER TABLE vehicle ADD CONSTRAINT fk_owner FOREIGN KEY (ownerID) REFERENCES Owner(ownerID);

404
Q

What SQL command is used to add a foreign key to an existing table?

A

ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);

405
Q

What does the key type “PRI” indicate in MySQL?

A

It indicates that the column is a primary key.

406
Q

What does the key type “UNI” indicate in MySQL?

A

It indicates that the column is a unique key.

407
Q

What does the key type “MUL” indicate in MySQL?

A

It indicates that the column can contain duplicate values and is part of a foreign key relationship.

408
Q

Differentiate between simple and composite attributes.

A

Simple attributes cannot be divided further (e.g., grade), while composite attributes can be split into sub-components (e.g., a name split into first and last name).

409
Q

What is a single-valued attribute?

A

A single-valued attribute can store only one value per entity instance, like a date of birth.

410
Q

Describe a multi-valued attribute and why it should be avoided.

A

A multi-valued attribute can store multiple values per entity instance, such as multiple email addresses. It should be avoided to maintain database normalization.

411
Q

What is a derived attribute?

A

A derived attribute’s value is calculated from other attributes, such as age derived from the date of birth.

412
Q

What are the three common anomalies in non-normalized databases?

A

Insert anomaly, update anomaly, and deletion anomaly.

413
Q

What is an insert anomaly?

A

An insert anomaly occurs when new data insertion requires additional data to be inserted, causing problems when a primary key column cannot contain empty fields.

414
Q

Can you give an example of an insert anomaly?

A

In a college enrollment table, you cannot add a new course without enrolling new students and assigning them IDs, as the student ID column is the primary key and cannot be empty.

415
Q

What is an update anomaly?

A

An update anomaly occurs when updating a record requires updating multiple other records, leading to potential data inconsistency if updates are missed.

416
Q

Can you give an example of an update anomaly?

A

If the director of a department changes, you need to update the director’s name for all students enrolled in that department. Missing any updates can lead to inconsistent data.

417
Q

What is a deletion anomaly?

A

A deletion anomaly occurs when deleting a record causes unintended deletions of other related records.

418
Q

Can you give an example of a deletion anomaly?

A

Deleting a student’s record may result in losing the records for the entire department they were associated with.

419
Q

How does normalization address anomalies in a database?

A

Normalization optimizes the database design by creating tables with a single purpose, thus fixing insert, update, and deletion anomalies.

420
Q

What are the benefits of database normalization?

A

Fixes insert, update, and deletion anomalies, simplifies writing SQL queries, and ensures data consistency and accuracy.

421
Q

Why is it important to address anomalies in database design?

A

To ensure efficient data management, prevent data duplication, avoid modification issues, and simplify data queries.

422
Q

Name the three fundamental normalization forms.

A

The three fundamental normalization forms are:
* First Normal Form (1NF)
* Second Normal Form (2NF)
* Third Normal Form (3NF)

423
Q

What issue does the First Normal Form (1NF) address?

A

The First Normal Form (1NF) addresses the issue of data atomicity, ensuring that each cell contains only a single instance of data and eliminating repeating data groups.

424
Q

How does the First Normal Form (1NF) ensure data atomicity?

A

The First Normal Form (1NF) ensures data atomicity by organizing related data into separate tables where each column cell contains only one single instance of data.

425
Q

What are the steps to normalize data to First Normal Form (1NF)?

A

Steps to normalize data to 1NF include:
1. Creating a separate table for entities (e.g., Patient Table, Doctor Table, Surgery Table).
1. Ensuring each column cell contains a single instance of data.
1. Removing repeating groups of data.

426
Q

Provide an example SQL statement to create a normalized Patient Table in 1NF.

A 
CREATE TABLE Patient (
    PatientID VARCHAR(10) NOT NULL, 
    PatientName VARCHAR(50), 
    SlotID VARCHAR(10) NOT NULL, 
    TotalCost Decimal,
    CONSTRAINT PK_Patient PRIMARY KEY (PatientID, SlotID)
);
427
Q

What are the characteristics of the unnormalized table in the example?

A

The unnormalized table has repeating groups of data, multiple instances of data in cells, and difficulties in updating, querying, and assigning unique primary keys.

428
Q

What is the main objective of the Second Normal Form (2NF)?

A

The main objective of 2NF is to avoid partial dependency relationships between data in a table.

429
Q

What is partial dependency?

A

Partial dependency occurs when a non-key attribute depends only on part of a composite primary key.

430
Q

When should you focus on partial dependencies in a table?

A

You should focus on partial dependencies in tables with composite primary keys.

431
Q

How can you identify partial dependency in a table?

A

Check if any non-key attributes depend only on part of the composite key.

432
Q

Give an example of a partial dependency in a patient table.

A

In the patient table, the patient’s name depends only on PatientID, and the total cost depends only on SlotID.

433
Q

How do you fix partial dependencies in a table?

A

Split the table to remove partial dependencies, ensuring that non-key attributes depend on the entire composite key.

434
Q

What is required for a relation to be in the Third Normal Form (3NF)?

A

It must already be in the Second Normal Form (2NF) and have no transitive dependency.

435
Q

What is a transitive dependency?

A

It occurs when a non-key attribute in a table is functionally dependent on another non-key attribute in the same table.

436
Q

Why is transitive dependency not allowed in the Third Normal Form?

A

Because changing one non-key attribute would require changes in another non-key attribute, which violates the normalization rules.

437
Q

What is the benefit of achieving the Third Normal Form in database design?

A

It helps develop a database that is easy to access and query, well-structured, well-organized, consistent, and without unnecessary data duplications.

438
Q

What is data atomicity?

A

The principle that each field in a table should contain only one single value.

SQL (45 decks)

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