Thursday, January 26, 2012

SQL Training Questions

1.Which of the following is true?
a.TRUNCATE TABLE is identical to DELETE statement without WHERE clause and both remove all rows in a table.
b.TRUNCATE TABLE has to be used along with a WHERE clause
c.TRUNCATE TABLE deletes table from a database

2. We refer to a join as a self-join when…
a. we are joining more than 2 tables
b. we are joining table to itself.
c. we are using left and right join together

3. What does SQL stand for?
a. Standard Query Language.
b. Strong Query Language
c. Strict Query Language
d. Structured Query Language.

4. Which of the following SQL statements deletes all rows in table called SalesData?

a. DELETE FROM SalesData
b. DELETE SalesData
c. DELETE ALL SalesData
d. DELETE * FROM SalesData

5. If you don't specify ASC or DESC after a SQL ORDER BY clause, the following is used by default:

a. There is no default value.
c. ASC

6. If you join a table to itself, what kind of join are you using?

a. You can't join a table to itself.
b. Selective Join.
c. Self Join

7. Can the SELECT clause list have a computed value like in the example below? SELECT CustomerName, UnitPrice * NumberofUnits FROM Sales 

a. Yes.
b. No

8. When inserting data in a table do you always have to specify a list of all column names you are inserting values for?

a. Yes
b. No

9. Which of the following SQL clauses is used to enter data into a SQL table?


10. Which of the following SQL clauses is used to select data from 2 or more tables?


11. The FROM SQL clause is used to…
a. specify search condition
b. specify what table we are selecting or deleting data from.
c. specify range for search condition

12. Can you use both HAVING and WHERE SQL clauses in one SQL statement?
a. No.
b. Yes.

13. Can you join a table to itself?
a. Yes.
b. No.

14. Which of the following 3 SQL statements is correct?

a. SELECT Username, Password FROM Users
b. SELECT Username, Password WHERE Username = 'user1'
c. SELECT Username AND Password FROM Users

15. What does the ALTER TABLE clause do?

a. used to delete a database table.
b. modifies a table definition by altering, adding, or deleting table columns.
c. used to insert data into database table.
d. deletes data from database table.

16. Which SQL keyword is used to retrieve a minimum value?

a. MIN
c. LOW

Friday, January 6, 2012

Database Models

Flat-File  Database Model

  • ideal for small amounts of data
  • human readable or edited by hand
  • good for simple lists
  • more costly in time and processing power
  • data split up using a common delimiter like comma or TAB
  • one line per records
  • very prone to corruption
  • no inherent locking mechanisms

Relational Database Model

  • MySQL, Microsoft SQL Server ,  Oracle, Access , Portgress SQL 
  • Tables represent real world objects, 
  • each field is an attribute of the object
  • table - students
  • field - firstname. lastname, Class, Age, Village
  • record- Nimal, Perera, 12A, 17, Penideniya

  • tables can be linked to each other (students --> subjects  , subjects > teachers,  students ---> marks <----Subjects <---- Teachers
  • databases designed efficiently
  • no duplication of any data; 
  • maintain database integrity.
  • Can be huge saving in file size, 
  • deals with large volumes of data. 
  • have  "built in" functions to help retrieve, sort and edit the data 
  • RDBMS - relational database management system
  • developed by E.F. Codd. 
  • A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. 
  • data and relations between them are organised in tables. 
  • A table is a collection of records and each record in a table contains the same fields. 
  • based on the Relational Algebra and set theory

Hierarchical  Database Model
  • Organizes data in a tree structure (parent and child data)
  • Data in a series of records, which have a set of field values attached to it. 
  • have record types. These record types are the equivalent of tables in the relational model,
  • individual records is the equivalent of rows. 
  • To create links between these record types,uses Parent Child Relationships. 
  • 1:N mapping between record types. 
  • uses Binary trees, like 

  • an organization  stores information on an employee ( name, employee number, department, salary) 
  • an store information about an employee's children, (name and date of birth)
  • The employee and children data forms a parent, child hierarchy
  • If an employee has three children, three child segments associated with one employee segment.
  • restricts a child segment to having only one parent segment. 
  • popular in late 1960s - 1970s.  (IBM)

Network Database

  • Some data modeled with more than one parent per child. 
  • permitts modeling of many-to-many relationships in data.
  • The data model is a simple network 
  • The CODASYL network model is based on mathematical set theory. 

Object/Relational Databases

  •  adds new object storage capabilities to the relational systems at the core of modern information systems. 
  • to integrate management of traditional fielded data with complex objects 
  • (geospatial data, audio, video, images, and applets)
  •  tabular structures and data definition languages (DDLs) w
  • SQL3, ODBC, JDBC (IBM, Informix, Sun and Oracle )

Object-Oriented Database Model

  • adds database functionality to object programming languages. 
  • useful when we have complex relationships between data
  • C++, Smalltalk and Java 
  • The power of the OODB comes from persistent data in databases, and transient data in executing programs.
  • object DBMSs have no performance overhead to store or retrieve interrelated objects.
  • mapping of object programming language objects to database objects 
  • provides higher performance
  • better management of the complex interrelationships between objects. 
  • financial portfolio risk analysis systems
  • telecommunications service applications
  • world wide web document structures, 
  • design and manufacturing systems
  • hospital patient record systems