- In an SQL query, which built-in function is used to total numeric columns?
A) AVG
B) COUNT
C) MAX
D) MEAN
E) SUM
- In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria?
A) AND
B) EXISTS
C) HAVING
D) IN
E) WHERE
- How to import table using "insert" statement?
INSERT INTO TempColorTable
SELECT code,ColorValue
FROM ColorTable
- What is the difference between a "where" clause and a "having" clause?
- What structure can you implement for the database to speed up table reads?
2] properly locate different DB objects across different tablespaces, files and so on.
3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
- What are the tradeoffs with having indexes?
2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
- What is a "constraint"?
There are four primary types of constraints that are currently supported by SQL Server:
PRIMARY/UNIQUE - enforces uniqueness of a particular table column.
DEFAULT - specifies a default value for a column in case an insert operation does not provide one.
FOREIGN KEY - validates that every value in a column exists in a column of another table.
CHECK - checks that every value stored in a column is in some specified list.
NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
- What is a "trigger"?
A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete)
A trigger is a database object directly associated with a particular table.
Basically, trigger is a set of SQL statements
3. Answer the short question below regarding this trigger. Any syntax errors are not intended and there may have been some liberties taken with the grammar.
[10 pts]
CREATE TRIGGER LogIt
AFTER UPDATE (balance) OR INSERT OR DELETE
ON accounts
FOR ROW
(UPDATE accounts
SET lastChanged = Now
WHERE acctNo = NEW.acctNo;
INSERT INTO accountsLog
VALUES (NEW.acctNo, NEW.balance, OLD.balance, Now, Username);
);
a. If an INSERT INTO accounts (acctNo, balance) VALUES (98765,1000.00) statement is executed, explain the actions of the trigger. [3]
The INSERT is followed by an UPDATE the same table that also sets a timestamp. The action is then logged into accountsLog with the new account number and balance (old balance will be NULL along with the timestamp and the user who made the change.
b. If an UPDATE accounts SET balance = balance*1.005 statement is executed, explain the actions of the trigger. [4]
Every account balance is increased by 0.5%. and for each account the updated timestamp is also applied. The log is updated with old and new balances with time stamp. Every account causes the trigger to fire.
c. Could this trigger loop forever since it is triggered on table accounts and it then updates table accounts? Why or why not? [3]
NO, the trigger updates the lastChanged attribute and not the balance attribute which won’t cause a loop
jcsites.juniata.edu/faculty/rhodes/dbms/exams/mid2f13key.docx
***************************************************************
- What is a view?
View is a precomplied SQL query which is used to select data from one or more tables
A view is like a table but it doesn’t physically take any space.
View can also be used to restrict users from accessing the tables directly.
The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted.
- What are stored-procedures? And what are the advantages of using them?
References
http://quizlet.com/5386117/database-processing-chapter-2-flash-cards/
http://www.dotnetfunda.com/interview/exclusive/ShowCatQuestion.aspx?start=15&page=2&category=38
http://www.geekinterview.com/question_details/32906
http://www.techinterviews.com/sql-interview-questions-and-answers
http://www.sqltutorial.org/sqlouterjoin.aspx
- Assume that you export a table from oracle which does not have primary key and you want to delete repeating records.
create table employee
(
EmpId number,
EmpName varchar2(10),
EmpSSN varchar2(11)
);
insert into employee values(1, 'Jack', '555-55-5555');
insert into employee values (2, 'Joe', '555-56-5555');
insert into employee values (3, 'Fred', '555-57-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');
insert into employee values (1, 'Jack', '555-55-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6 ,'Lisa', '555-70-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');
DELETE FROM employee
WHERE rowid not in
(SELECT MIN(rowid)
FROM employee
GROUP BY EmpId, EmpName, EmpSSN);
http://stackoverflow.com/questions/985384/delete-duplicate-records-from-a-sql-table-without-a-primary-key
- What's the difference between "IN" and "EXISTS"?
IN:
Returns true if a specified value matches any value in a subquery or a list.
Exists:
Returns true if a subquery contains any rows.
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx
In SQL, what are the differences between EXISTS and IN when addressing sub-queries? (2
points)
⇒ Answer: EXISTS R is a condition that is true if and only if R is NOT empty. r IN R is
true if and only if r is equal to one of the values in R
webcache.googleusercontent.com/search?q=cache:8vRupC7L9OYJ:https://wiki.engr.illinois.edu/download/attachments/227743489/CS411-F2011-Final-Sol.pdf%3Fversion%3D1%26modificationDate%3D1380470739000+&cd=3&hl=tr&ct=clnk&gl=tr&client=firefox-a
- Create a table on mssql server with some data
CREATE TABLE [dbo].[employee2](
[empid] [numeric](18, 0) NOT NULL,
[empname] [nvarchar](50) NULL,
[empssn] [nvarchar](50) NULL,
[empdept] [nvarchar](50) NULL,
[emptitle] [nvarchar](50) NULL,
[empsal] [numeric](18, 0) NULL
)
insert into employee2 values( 1 ,'Jack','555555555','HR','specialist', 1200 );
insert into employee2 values( 2 ,'Joe','555555556','Management','director', 2000 );
insert into employee2 values( 3 ,'Fred','555555557','Reception','specialist', 1400 );
insert into employee2 values( 4 ,'Lisa','555555558','IT','specialist', 3000 );
insert into employee2 values( 5 ,'Cathy','555555559','Support','specialist', 2500 );
insert into employee2 values( 6 ,'Mike','555555560','Finance','director', 1400 );
insert into employee2 values( 7 ,'Fred','555555561','Management','director', 3000 );
insert into employee2 values( 8 ,'Lisa','555555562','Reception','specialist', 2500 );
insert into employee2 values( 9 ,'Cathy','555555563','IT','director', 2300 );
list max min and average salaries in IT department
SELECT MIN(EmpSal), MAX(EmpSal), AVG(EmpSal)
FROM employee2
WHERE EmpDept='IT';
list the number of employees in IT department
select count(*)
FROM employee2
WHERE EmpDept='IT';
list the highest paid salaries based on department
SELECT EmpDept,MAX(EmpSal)
FROM employee2
GROUP BY EmpDept;
list the salaries based on department
SELECT EmpDept,SUM(EmpSal) FROM employee2
GROUP BY EmpDept;
list the employees who has the same title but different salaries
select distinct(a.empsal),a.emptitle
from employee2 a
where a.emptitle in
(select b.emptitle
from employee2 b
where b.emptitle=a.emptitle
)
order by a.emptitle asc;
- How does one escape special characters when writing SQL queries?
Use two quotes for every one displayed. Examples:
SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
TEXT
--------------------
Franks's Oracle site
SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
TEXT
----------------
A 'quoted' word.
SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
TEXT
-------------------------
A ''double quoted'' word.
References:
http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F
- how to add data into a view in oracle.In this example as you add data into view you add also into the table on which view is based
create table Employee2(
ID VARCHAR2(4 BYTE) NOT NULL,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
);
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
;
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
;
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
;
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
;
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
;
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
;
insert into Employee2(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
;
select * from Employee2;
CREATE VIEW Employee2_view AS
SELECT id, first_name, last_name
FROM Employee2;
select * from Employee2_view;
INSERT INTO Employee2_view (id, first_name, last_name) VALUES (
13, 'New', 'Westerrn');
http://www.java2s.com/Tutorial/Oracle/0160__View/PerforminganINSERTUsingaView.htm
- UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'
http://www.w3schools.com/sql/sql_update.asp
- SQL query to identify list of duplicate rows
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
The first step is to identify which rows have duplicate primary key values:
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1
Select the duplicate key values into a holding table. For example:
create table holdkey(col1 int,col2 int,counter int );
insert into holdkey select col1,col2,count(*)
from t1
group by col1,col2
having count(*)>4 ;
Select the duplicate rows into a holding table, eliminating duplicates in the process. For example:
create table holdups(col1 int,col2 int,col3 char(50) );
insert into holdups
select distinct t1.col1,t1.col2,t1.col3
from t1,holdkey
where t1.col1=holdkey.col1
and t1.col2=holdkey.col2;
At this point, the holddups table should have unique PKs,
SELECT col1, col2, count(*)
FROM holdups
GROUP BY col1, col2
Delete the duplicate rows from the original table. For example:
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
- 4. Below is a JDBC application (which should look vaguely familiar). Give short answers the questions below referring to this application, or fill in the blanks as directed.
import java.io.*;
import java.sql.*;
public class dbdemo {
public static void main (String[] args) throws IOException{
String connString = null;
String input =null;
int population,votes;
String stateName = null;
BufferedReader br = new BufferedReader( new InputStreamReader(System.in));
Connection conn = null;
Statement stmt = null;
ResultSet res = null;
PreparedStatement ps = null;
String query = "SELECT P.PRES FROM PRESIDENTS P";
String pQuery = "SELECT * FROM STATES WHERE POP BETWEEN ? AND ?";
connString = "jdbc:postgresql://itcsserver/pres?user=guest&password=guest ";
try {
Class.forName("org.postgresql.Driver"); //****A****
conn = DriverManager.getConnection(connString ); //****B****
stmt = conn.createStatement();
res = stmt1.executeQuery(query);
while (res.next()){
System.out.println(res.getString("PRES"));
}
ps = conn.prepareStatement( pQuery );
System.out.print("Enter minimum population level: ");
input = br.readLine();
ps.setInt(1, Integer.parseInt(input)); //****C****
System.out.print("Enter maximum population level: ");
input = br.readLine();
ps.setInt( 2, Integer.parseInt(input));
res = ps.executeQuery();
while(res.next()){ //****D****
stateName = res.getString("STATE");
population = res.getInt("POP");
votes = res.getInt("STATES_VOTES"); //****E****
System.out.println(stateName + " has population "+population
+" and holds "+votes+" electoral votes");
}
} catch (Exception e) {
System.out.println(e.toString());
}
System.exit(0);
}
}
a. What is the purpose of the Java code line labeled “A”. [4]
This loads the driver software dynamically matching the database server type to the Java code
b. List 3 ways the connection action in Java code line labeled “B” can fail. [6]
Wrong path, wrong protocol, wrong username or unauthorized, bad password, network not available
c. Explain the actions of line labeled “C”. [3]
It fills in the first ? with a numerically converted string into the SQL statement
d. Explain the next() method in the line labeled “D” [4]
The cursor is advanced to the next or first tuple (if first loop). Access to the result set is limited to this tuple. If there are no more tuples, null/false is returned.
e. Describe two exceptions that can be thrown by a resultSet.getInt(“attrName”) method call such as found in line labeled “E”. [3]
bad attribute name, attribute can be made as an integer, resultset tuple not exist
jcsites.juniata.edu/faculty/rhodes/dbms/exams/mid2f13key.docx
No comments:
Post a Comment