Chapter 8 : Database

Question 1

What does database provides?

Answer 1

1. Provides a structured manner in which data can be stored, retrieve and for further processing

Question 2

What state is data stored inside variable?

Answer 2

1. Volatile

Question 3

What is a Database Management System?

Answer 3

1. Software that manages large collections of data

Question 4

What is Database Management System designed to?

Answer 4

1. Store Data
2. Retrieve Data
3. Manipulate Data

Question 5

List out the 3 layer manipulation for C# application can interact with

Answer 5

1. Application
   
   • Interacts with the user and sends instructions to DBMS
2. DBMS
   
   • Works directly with the data and sends the result back to the application
3. Data
   
   • The data stored in DBMS

Question 6

What is the data type used to represent True / False value
1. SQL Server Data Type
2. C#

Answer 6

1. bit
2. bool

Question 7

What is the data type used to represent date and time value
1. SQL Server Data Type
2. C#

Answer 7

1. datetime
2. DateTime

Question 8

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#

Answer 8

1.decimal ( t , d )
2. decimal

Question 9

What is the data type used to represent real numbers
1. SQL Server Data Type
2. C#

Answer 9

1. float
2. double

Question 10

What is the data type used to represent an integer number
1. SQL Server Data Type
2. C#

Answer 10

1. int
2. int

Question 11

What is the data type used to represent currency?
1. SQL Server Data Type
2. C#

Answer 11

1. money
2. decimal

Question 12

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#

Answer 12

1. char(n)
2. string

Question 13

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#

Answer 13

1. varchar(n)
2. string

Question 14

What does most database have?

Answer 14

1. Primary Key

Question 15

What is Primary Key used? ( 2 )

Answer 15

1. Column that can be used to identify a specific row
2. 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

Question 16

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 )

Answer 16

1. Create an identity column specifically to serve as the primary key ( Identify Column )
2. Identify column typically contain integers
3. Each time a new row is addded to the table, the DBMS automatically assigns a unique value to the identify column

Question 17

What is a table?

Answer 17

1. 2 dimentional container made of rows and columns

• Data stored in a database are organized into tables, rows and columns

Question 18

What is a row or a column?

Answer 18

1. A row is a complete set of information about a single term
2. A column holds an individual piece of information about the item ( Metadata )

Name Phone
Jil 555-5678

Question 19

List out the layers that .NET applications uses to connect to a database

Answer 19

1. Application
2. Binding Source
   
   • A component that can connect user interface controls directly to a dataset
3. Dataset
   
   • Gets a copy of a table from the table adapter and keeps the copy of the table in memory
4. Table Adapter
   
   • Connets to a data source and retrieves data from a table in a data source
5. Data Source
   
   • A source of data with which the application can work

Question 20

What databases that Visual Studio provides that make it easy to create and configure the database?

Answer 20

1. Server-Based Database

Question 21

What information that the connection string includes?

Answer 21

1. Include information on data provides to be used

Question 22

What is the syntax for connection string?

Answer 22

SqlConnection connection = new SqlConnection(connectionString)

Question 23

What is the full name for SQL?

Answer 23

1. Structured Query Language

• Standard language for working with database management system

Question 24

What parts of SQL involves Data Definition Language? ( 3 )

Answer 24

1. Create
2. Alter
3. Drop DB & Table

Question 25

What parts of SQL involves Data Manipulation Language? ( 4 )

Answer 25

1. Create 2. Read 3. Update 4. Delete Records

Question 26

List out 2 syntax to insert record into SQL

Answer 26

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 )

Question 27

How to read records from SQL?

Answer 27

SELECT column FROM table_name SELECT Description FROM Product SELECT Description, Price FROM Product SELECT * FROM Product ( Every Column )

Question 28

How to select values from a table where the value isn't repeated?

Answer 28

1. Use SELECT DISTINCT SELECT DISTINCT Title, Price FROM Book;

Question 29

What clause that I need to use to narrow the list down to few selected rows in the table? Also point out the format

Answer 29

1. WHERE clause SELECT Columns FROM Table WHERE Criteria SELECT * FROM Product WHERE Price > 20.00

Question 30

How to retrieve all the columns from only rows where the description is "Denim Jeans"?

Answer 30

SELECT * FROM Product WHERE Description = "Denim Jeans"

Question 31

List out the Relational Operators that WHERE clause can use ( 9 )

Answer 31

1. = 2. <> - Not Equal 3. > 4. >= 5. < 6. <= 7. BETWEEN 8. LIKE 9. IN

Question 32

List out the Logical Operators that WHERE clause can use ( 3 )

Answer 32

1. AND 2. OR 3. NOT

Question 33

What does the AND operators requires? Give an example on how to select price > 20 and price < 30

Answer 33

1. Requires both search criteria to be true for a row to be qualified as a match 2. SELECT * FROM Product WHERE Price > 20 AND Price < 30

Question 34

What does the Or operators requires? Give an example on how to select price > 20 or product number < 10

Answer 34

1. Requires that either of the search criteria to be true for a row to be qualified as a match 2. SELECT * FROM Product WHERE Price > 20 OR Product_Number < 10

Question 35

What does the NOT operator disqualify? Give an example on how to select price where the price is not 20

Answer 35

1. Disqualify a search criteria 2. SELECT * FROM Product WHERE NOT Price = 20

Question 36

What does the LIKE operator allows?

Answer 36

1. 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

Question 37

How to write SQL Statement to search on Description has the string Shirt?

Answer 37

SELECT * FROM Product WHERE Description LIKE "%Shirt%"

Question 38

List out 2 wildcard character that the LIKE operator can use

Answer 38

1. % 2. _ ( Underscore )

Question 39

What does % represents when using the LIKE operator?

Answer 39

1. Any sequence of zero or more characters SELECT * FROM Product WHERE Description LIKE ‘%Shirt%’

Question 40

What does _ represents when using the LIKE operator?

Answer 40

1. Represents a single character SELECT * FROM Product WHERE Product_Number LIKE ‘2_-0_’ * Where product number have 2_-0_ ( 21-01 , 22-02, ... )

Question 41

How to select a customer name that start with a?

Answer 41

1. WHERE Customer_Name LIKE "a%"

Question 42

How to select a customer name that ends with a?

Answer 42

1. WHERE Customer_Name LIKE "%a"

Question 43

How to select a customer name that have or in any position?

Answer 43

1. WHERE Customer_Name LIKE "%or%"

Question 44

How to select a customer name that have r in second position?

Answer 44

1. WHERE Customer_Name LIKE "_r%"

Question 45

How to select a customer name that start with a and are least 3 characters in length?

Answer 45

1. WHERE Customer_Name LIKE "a_%_%" 1. WHERE Customer_Name LIKE "a___%"

Question 46

How to select a customer name that start with a and end with o?

Answer 46

1. WHERE CustomerName LIKE "a%o"

Question 47

Until Slide 30