3. Database Usage and SQL Basics
40 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the purpose of the condition 'A.Nr < B.Nr' in the SQL query for retrieving names of executors with the same qualification?

It is used to eliminate duplicate results by ensuring that each pair of names is listed only once.

In SQL, how can you retrieve the names of executors participating in project number 1?

You can use the query: SELECT Pavarde FROM Vykdytojai WHERE Nr IN (SELECT Vykdytojas FROM Vykdymas WHERE Projektas = 1).

What is the purpose of the CREATE TABLE statement in SQL?

To define a new table and its columns in a database.

What is the difference between CHAR(n) and VARCHAR(n) in SQL?

<p><code>CHAR(n)</code> is fixed-length, while <code>VARCHAR(n)</code> is variable-length.</p> Signup and view all the answers

What is a primary key in a database?

<p>A primary key is a column or set of columns that uniquely identifies each row in a table, prohibiting duplicate or NULL values.</p> Signup and view all the answers

Provide an example SQL statement to create a primary key on a column 'ID' in a table 'TEST'.

<p>The statement is: <code>CREATE TABLE TEST(ID INT PRIMARY KEY)</code>.</p> Signup and view all the answers

Explain what the NOT NULL constraint does in a table definition.

<p>It ensures that a column cannot have NULL values.</p> Signup and view all the answers

What does the DEFAULT keyword specify in a table definition?

<p>It defines a default value for a column if no value is provided during insertion.</p> Signup and view all the answers

What is the function of a foreign key in a relational database?

<p>A foreign key is a constraint that ensures a column's values correspond to values in the primary key column of another table.</p> Signup and view all the answers

How do you create an index on a column 'Pavarde' in the table 'Vykdytojai'?

<p>You can create the index using: <code>CREATE INDEX PAVARDZIU_IDX ON VYKDYTOJAI(PAVARDE)</code>.</p> Signup and view all the answers

Describe the use of AUTO_INCREMENT in a SQL table.

<p><code>AUTO_INCREMENT</code> automatically generates a unique value for a column, typically used for ID fields.</p> Signup and view all the answers

What data type would you use to store a decimal value in SQL, and how is it defined?

<p>The <code>DECIMAL(p, s)</code> data type is used, defining precision 'p' and scale 's'.</p> Signup and view all the answers

Explain why it is not advisable to index frequently changing columns in a database.

<p>Indexing frequently changing columns is inefficient as it requires constant updates to the index, which can degrade performance.</p> Signup and view all the answers

What is database design and why is it important?

<p>Database design is the process of defining the structure of a database, which is crucial for creating an effective and accurate database system that meets user needs.</p> Signup and view all the answers

What does the UNIQUE constraint ensure for a column in a table?

<p>It ensures that all values in the column are distinct and no duplicates are allowed.</p> Signup and view all the answers

Identify the SQL data type suitable for storing date and time values.

<p>The <code>DATE</code>, <code>TIME</code>, and <code>TIMESTAMP</code> data types are suitable.</p> Signup and view all the answers

Explain the purpose of the ALTER TABLE statement in SQL.

<p>The ALTER TABLE statement is used to modify the structure of an existing table, allowing changes such as adding, dropping, or altering columns.</p> Signup and view all the answers

What is the key difference between using WHERE and JOIN in SQL queries?

<p>WHERE filters records based on conditions, while JOIN combines rows from two or more tables based on related columns.</p> Signup and view all the answers

What is meant by the 'JOIN condition' in SQL?

<p>The JOIN condition specifies the common field(s) in the tables that are used to combine records, ensuring relevant data is associated.</p> Signup and view all the answers

Describe the output of an INNER JOIN.

<p>An INNER JOIN returns only the rows where there is a match in both tables involved in the join.</p> Signup and view all the answers

What is the effect of using a LEFT OUTER JOIN?

<p>A LEFT OUTER JOIN returns all records from the left table and the matched records from the right table; unmatched records from the right will return NULL.</p> Signup and view all the answers

In a query to retrieve project managers, how would you include managers not involved in any projects using JOIN?

<p>You would use a LEFT OUTER JOIN to ensure all managers are listed, regardless of their involvement in projects.</p> Signup and view all the answers

Explain the function of FULL OUTER JOIN in database queries.

<p>FULL OUTER JOIN returns all records from both tables, with NULLs in place for unmatched records from either side.</p> Signup and view all the answers

How can tables be joined with themselves, and why would this be useful?

<p>Tables can be joined with themselves using a self-join, which is useful for comparing rows within the same table, like finding hierarchical relationships.</p> Signup and view all the answers

What are the three essential questions to consider when starting to create a database?

<ol> <li>What data should the database collect? 2. What information do users want to know about the data? 3. What actions will users want to perform with the data?</li> </ol> Signup and view all the answers

Why is it said that not planning a database can lead to wasting more time later?

<p>Failing to plan upfront can result in increased time spent troubleshooting and redesigning the database later.</p> Signup and view all the answers

List the types of tables needed for a library database.

<p>Readers, Authors, Books, and Rental Agreements.</p> Signup and view all the answers

What is the role of primary keys in database tables?

<p>Primary keys serve as unique identifiers for each record in the table.</p> Signup and view all the answers

How should data fields in tables be determined?

<p>Data fields should be precisely what is necessary for the object being described, avoiding excess or insufficiency.</p> Signup and view all the answers

Explain the principle regarding the repetition of information in database tables.

<p>There should be no repeating information within a table, and every detail must be stored in only one place.</p> Signup and view all the answers

What data does a library database need to track about its books?

<p>It must track the titles, authors, rental status, and borrower information.</p> Signup and view all the answers

What happens after defining the database's purpose?

<p>You proceed to define the tables needed to collect data on the relevant objects.</p> Signup and view all the answers

What is the role of primary keys in establishing relationships between tables in a database?

<p>Primary keys uniquely identify rows in a table and are inserted into other tables as foreign keys to form relationships.</p> Signup and view all the answers

Explain the purpose of database normalization.

<p>Database normalization aims to minimize data redundancy, ensure efficient data management, and enhance data integrity.</p> Signup and view all the answers

What distinguishes the first normal form (1NF) from unnormalized data?

<p>1NF requires that each database field contains only atomic values and eliminates repeating groups.</p> Signup and view all the answers

Characterize the significance of moving non-key-dependent information into separate tables.

<p>This action achieves the second normal form (2NF) by ensuring that all non-key information is directly dependent on the primary key.</p> Signup and view all the answers

In what scenario might a 1:1 relationship be utilized in a database?

<p>A 1:1 relationship may be used for security purposes or when specific fields in one table are often empty.</p> Signup and view all the answers

Define the third normal form (3NF) in the context of database normalization.

<p>3NF requires that every field in a table is functionally dependent only on the primary key and not on other non-key fields.</p> Signup and view all the answers

Why is it important to check for potential flaws in a database structure after initial creation?

<p>Examining for flaws ensures that the database can effectively retrieve the desired information without issues.</p> Signup and view all the answers

What is the role of foreign keys in a relational database?

<p>Foreign keys create a link between tables by referencing the primary key of another table.</p> Signup and view all the answers

Flashcards

CREATE TABLE statement

Used to create a database table in SQL.

Database data types

Types of data a database can store, such as text, numbers, dates, and binary.

VARCHAR(n)

Variable-length character string data type (e.g., a person's name).

INTEGER data type

Whole number data type (e.g., employee ID).

Signup and view all the flashcards

NOT NULL constraint

Ensures a column cannot contain null values.

Signup and view all the flashcards

AUTO_INCREMENT

A column automatically increases with each new row.

Signup and view all the flashcards

UNIQUE constraint

Column values must be unique.

Signup and view all the flashcards

DECIMAL(p, s)

Data type for storing decimal numbers with precision (p) and scale (s).

Signup and view all the flashcards

SQL ALTER TABLE

Used to modify the structure of a table in a database, including adding, dropping, or changing columns or constraints.

Signup and view all the flashcards

Table JOIN

Combines rows from two or more tables based on a related column between them. Used to retrieve data from multiple tables.

Signup and view all the flashcards

INNER JOIN

A type of JOIN that only returns rows where the join condition is met in both tables.

Signup and view all the flashcards

LEFT OUTER JOIN

All rows from the left table (i.e., the first table in the join), plus matching rows from the right table. If no match in the right table, results are completed with NULL values

Signup and view all the flashcards

RIGHT OUTER JOIN

All rows from the right table, plus matching rows from the left side. If no match on the left table, result is completed with NULL values.

Signup and view all the flashcards

JOIN condition

The criteria used to connect rows from two or more tables based on a shared column value.

Signup and view all the flashcards

Multiple Table JOINs

Connects more than two tables in a query based on multiple join conditions.

Signup and view all the flashcards

SQL SELECT statement

A statement used to retrieve data from a database.

Signup and view all the flashcards

Primary Key

A column or set of columns that uniquely identifies each row in a table, preventing duplicate values and NULLs.

Signup and view all the flashcards

Foreign Key

A column or columns that refer to a primary key in another table, enforcing referential integrity. It links tables together.

Signup and view all the flashcards

Indexes

Data structures used to speed up data retrieval in databases, like pointers in books. They allow faster searching by using a specific column only, without looking at the entire row.

Signup and view all the flashcards

SQL JOIN

SQL command used to combine rows from two or more tables based on a related column between them.

Signup and view all the flashcards

SQL SELECT

SQL keyword used to query and retrieve data from a database table or multiple tables.

Signup and view all the flashcards

Database Design

Planning the structure and organization of a database before its creation, ensuring it is efficient, accurate, and meets the needs of the application.

Signup and view all the flashcards

SQL WHERE clause

A clause used in SQL queries to filter the rows returned by a query based on a specified condition

Signup and view all the flashcards

SQL IN operator

A clause used in SQL to check if a value falls within a specific set of values.

Signup and view all the flashcards

Database Design Process

A structured approach to creating a database, involving planning and defining tables, fields, and relationships.

Signup and view all the flashcards

Database Purpose

The intended function and usage of the database. It answers questions about what data will be stored, what information users will need, and what actions they will perform.

Signup and view all the flashcards

Database Table

A structured list of information about a specific subject (e.g., books, readers).

Signup and view all the flashcards

Table Field

A specific piece of information about an object in a table (e.g., book title, author name).

Signup and view all the flashcards

Unique Field

A field containing data that must be unique for every row (record) within the table. Used to avoid redundancies.

Signup and view all the flashcards

Table Relationship

How tables in a database connect to each other. This links information between tables for comprehensive data.

Signup and view all the flashcards

Atomic Concept

A fundamental concept that can't be broken down further into simpler concepts.

Signup and view all the flashcards

Multi-field Key

A combination of values from multiple fields to uniquely identify a record.

Signup and view all the flashcards

Database Normalization

A process to organize data in a database to reduce data redundancy and improve data integrity.

Signup and view all the flashcards

1NF (First Normal Form)

Each cell contains only one value. No repeating groups of values in a single column.

Signup and view all the flashcards

2NF (Second Normal Form)

Meets the requirements of 1NF, and data not directly dependent on the primary key is moved to a separate table.

Signup and view all the flashcards

3NF (Third Normal Form)

Meets the requirements of 2NF, and removes columns whose values are determined by other columns in the table.

Signup and view all the flashcards

Relationship Types

Describes how tables in a database are linked. Common types include 1:1, 1:many, and many:many.

Signup and view all the flashcards

Study Notes

Database Usage, SQL

  • Databases use SQL for data management.
  • SQL is used to design databases.

Table Creation

  • Tables are created using CREATE TABLE statements.
  • The structure follows: CREATE TABLE table_name (column1 type1, column2 type2, ...)
  • Supported data types include:
    • Textual data
    • Binary data
    • Numbers
    • Dates
    • Times

Character Data Types

  • Character strings can hold a specific number of characters.
  • Fixed-length strings:
    • CHAR(n) - n characters up to 254 (2,147,483,647).
  • Variable-length strings:
    • VARCHAR(n) or CHARACTER VARYING(n) - n characters, up to 2,147,483,647.
    • CLOB(n[K|M|G]) - up to 2 GB.

Numeric Data Types

  • Integer types:
    • SMALLINT: Integer values from -32,768 to 32,767.
    • INTEGER (4 bytes): Integer values from -2,147,483,648 to 2,147,483,647.
    • BIGINT (8 bytes): Larger integer values.
  • Floating-point types:
    • REAL (32 bytes): Floating-point numbers.
    • FLOAT or DOUBLE: Larger floating-point values.
  • Decimal:
    • DECIMAL (p, s) or NUMERIC: Stores decimal numbers with precision p and scale s

Binary Data Types

  • Fixed-length binary data:
    • BINARY(n) - up to 1 billion bytes.
  • Variable-length binary data:
    • VARBINARY(n) - up to 1 billion bytes.
    • BLOB(n[K|M|G]) - up to 2 GB.

Dates and Times

  • DATE: Stores dates.
  • TIME: Stores time values.
  • TIMESTAMP: Stores both date and time.

Table Creation (Examples)

  • Example CREATE TABLE statement for employees: CREATE TABLE Employees (EmployeeID BIGINT NOT NULL, FirstName VARCHAR(20), LastName VARCHAR(30), HireDate DATE, Salary DECIMAL(10, 2) DEFAULT 0)

Table Modifications

  • ALTER TABLE statements allow modifying existing tables
  • Add a column: ALTER TABLE table_name ADD column_name data_type
  • Drop a column: ALTER TABLE table_name DROP column_name
  • Change column properties: ALTER TABLE table_name ALTER column_name SET DEFAULT value

Table Joins (WHERE)

  • WHERE clauses are used to filter data from joins.
  • SELECT column1, column2 FROM table1, table2 WHERE condition.

Table Joins (JOIN)

  • Different ways to combine data from multiple tables
  • JOIN simplifies conditions from multiple tables.
  • Uses ON clause, e.g., SELECT column1, column2 FROM table1 JOIN table2 ON table1.columnX = table2.columnY.

Structured Queries

  • Structured query language statements often use subqueries
  • IN operator is used in selecting values from a subquery

Primary Keys

  • Ensure unique identification of rows in a table.
  • The PRIMARY KEY constraint prevents duplicate values.
  • Examples:
    • CREATE TABLE Customers (CustomerID INT PRIMARY KEY, ...)
    • ALTER TABLE Orders ADD CONSTRAINT pk_order PRIMARY KEY (OrderID)

Foreign Keys

  • Foreign keys create a link between two tables.
  • A foreign key references a primary key in a foreign table.
  • ALTER TABLE Orders ADD CONSTRAINT fk_order_customer FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ON DELETE CASCADE

Indexes

  • Data structures used for faster lookups in databases.
  • Indexes enhance search speed.
  • They are useful for large databases.
  • Useful for speeding up the lookups of data.
    • CREATE INDEX idx_name ON table_name(column_name)
    • DROP INDEX idx_name

Database Design

  • Designing a database is crucial before actually creating it
  • Consider the purpose, data requirements, and user needs

Database Design Process

  • Define the database purpose
  • Identify required tables
  • Define column data types
  • Determine relationships between tables
  • Refine the initial design

Database Purpose

  • Clarify what the database will track.
  • Specify the data users will need.
  • Detail data manipulation requests.

Table Definitions

  • Define entities and attributes.
  • Aim for each table representing one object.
  • Avoid redundant information in one table.
  • Should be related in a database.

Data Elements in Tables

  • Determine data attributes for each object.
  • Keep data elements concise.

Database Normalization

  • Reduce redundant data and dependency issues.
  • Create separate tables for related data
  • Normalization processes include Normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF) to ensure logical and data organization

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

Duomenų bazių naudojimas, SQL PDF

Description

This quiz covers the fundamentals of SQL usage in database management, including how to create tables and understand various data types. It explores character and numeric data types, providing essential knowledge for database design and implementation.

More Like This

Database Creation and Usage Quiz
10 questions

Database Creation and Usage Quiz

BonnyWhistle avatar
BonnyWhistle
2.1. Database Usage and SQL DML Statements
16 questions

2.1. Database Usage and SQL DML Statements

MagnanimousCloisonnism avatar
MagnanimousCloisonnism
Introduction to Programming Languages
5 questions

Introduction to Programming Languages

NobleNumber9130 avatar
NobleNumber9130
Use Quizgecko on...
Browser
Open
Browser
Browser