Chapter 8 : Database Flashcards
What does database provides?
- Provides a structured manner in which data can be stored, retrieve and for further processing
What state is data stored inside variable?
- Volatile
What is a Database Management System?
- Software that manages large collections of data
What is Database Management System designed to?
- Store Data
- Retrieve Data
- Manipulate Data
List out the 3 layer manipulation for C# application can interact with
- Application
- Interacts with the user and sends instructions to DBMS
- DBMS
- Works directly with the data and sends the result back to the application
- Data
- The data stored in DBMS
What is the data type used to represent True / False value
1. SQL Server Data Type
2. C#
- bit
- bool
What is the data type used to represent date and time value
1. SQL Server Data Type
2. C#
- datetime
- DateTime
What is the data type used to represent decimal value with total digits appearing after the decimal point
1. SQL Server Data Type
2. C#
1.decimal ( t , d )
2. decimal
What is the data type used to represent real numbers
1. SQL Server Data Type
2. C#
- float
- double
What is the data type used to represent an integer number
1. SQL Server Data Type
2. C#
- int
- int
What is the data type used to represent currency?
1. SQL Server Data Type
2. C#
- money
- decimal
What is the data type used to represent a fixed-length Unicode string with a maximun length of n character?
1. SQL Server Data Type
2. C#
- char(n)
- string
What is the data type used to represent a variable-length Unicode string with a maximun length of n character?
1. SQL Server Data Type
2. C#
- varchar(n)
- string
What does most database have?
- Primary Key
What is Primary Key used? ( 2 )
- Column that can be used to identify a specific row
- The column that is designed as the primary key must hold a unique value for each row
- Example
EmployeeID, Social Security Number, Invoice Number, Sales Order Number
How to solve issues for data I want to store in a table does not contain any unique items that can be used as a primary keys? ( 2 )
- Create an identity column specifically to serve as the primary key ( Identify Column )
- Identify column typically contain integers
- Each time a new row is addded to the table, the DBMS automatically assigns a unique value to the identify column
What is a table?
- 2 dimentional container made of rows and columns
- Data stored in a database are organized into tables, rows and columns
What is a row or a column?
- A row is a complete set of information about a single term
- A column holds an individual piece of information about the item ( Metadata )
Name Phone
Jil 555-5678
List out the layers that .NET applications uses to connect to a database
- Application
- Binding Source
- A component that can connect user interface controls directly to a dataset
- Dataset
- Gets a copy of a table from the table adapter and keeps the copy of the table in memory
- Table Adapter
- Connets to a data source and retrieves data from a table in a data source
- Data Source
- A source of data with which the application can work
What databases that Visual Studio provides that make it easy to create and configure the database?
- Server-Based Database
What information that the connection string includes?
- Include information on data provides to be used
What is the syntax for connection string?
SqlConnection connection = new SqlConnection(connectionString)
What is the full name for SQL?
- Structured Query Language
- Standard language for working with database management system
What parts of SQL involves Data Definition Language? ( 3 )
- Create
- Alter
- Drop DB & Table
What parts of SQL involves Data Manipulation Language? ( 4 )
- Create
- Read
- Update
- Delete Records
List out 2 syntax to insert record into SQL
INSERT INTO table_name
VALUES ( value1, value2, … )
INSERT INTO table_name(col1, col2, col3, …)
VALUES ( value1, value2, value3)
INSERT INTO Book
VALUES ( 1, ‘Learn ABC’, 20.00 )
How to read records from SQL?
SELECT column FROM table_name
SELECT Description FROM Product
SELECT Description, Price FROM Product
SELECT * FROM Product ( Every Column )
How to select values from a table where the value isn’t repeated?
- Use SELECT DISTINCT
SELECT DISTINCT Title, Price FROM Book;
What clause that I need to use to narrow the list down to few selected rows in the table? Also point out the format
- WHERE clause
SELECT Columns FROM Table WHERE Criteria
SELECT * FROM Product WHERE Price > 20.00
How to retrieve all the columns from only rows where the description is “Denim Jeans”?
SELECT * FROM Product WHERE Description = “Denim Jeans”
List out the Relational Operators that WHERE clause can use ( 9 )
- =
- <> - Not Equal
- >
- > =
- <
- <=
- BETWEEN
- LIKE
- IN
List out the Logical Operators that WHERE clause can use ( 3 )
- AND
- OR
- NOT
What does the AND operators requires? Give an example on how to select price > 20 and price < 30
- Requires both search criteria to be true for a row to be qualified as a match
- SELECT * FROM Product WHERE Price > 20 AND Price < 30
What does the Or operators requires? Give an example on how to select price > 20 or product number < 10
- Requires that either of the search criteria to be true for a row to be qualified as a match
- SELECT * FROM Product WHERE Price > 20 OR Product_Number < 10
What does the NOT operator disqualify? Give an example on how to select price where the price is not 20
- Disqualify a search criteria
- SELECT * FROM Product WHERE NOT Price = 20
What does the LIKE operator allows?
- Allows me to do a search on a pattern rather than specifying exactly what is desired
- Both “Oxford Shirt” and “UNIQLO Shirt” both contains the string shirt
How to write SQL Statement to search on Description has the string Shirt?
SELECT * FROM Product WHERE Description LIKE “%Shirt%”
List out 2 wildcard character that the LIKE operator can use
- %
- _ ( Underscore )
What does % represents when using the LIKE operator?
- Any sequence of zero or more characters
SELECT * FROM Product WHERE Description LIKE ‘%Shirt%’
What does _ represents when using the LIKE operator?
- Represents a single character
SELECT * FROM Product WHERE Product_Number LIKE ‘2-0’
- Where product number have 2-0 ( 21-01 , 22-02, … )
How to select a customer name that start with a?
- WHERE Customer_Name LIKE “a%”
How to select a customer name that ends with a?
- WHERE Customer_Name LIKE “%a”
How to select a customer name that have or in any position?
- WHERE Customer_Name LIKE “%or%”
How to select a customer name that have r in second position?
- WHERE Customer_Name LIKE “_r%”
How to select a customer name that start with a and are least 3 characters in length?
- WHERE Customer_Name LIKE “a_%_%”
- WHERE Customer_Name LIKE “a___%”
How to select a customer name that start with a and end with o?
- WHERE CustomerName LIKE “a%o”
Until Slide 30
