141⟩ Why is it better to use an integrity constraint to validate data in a table than to use a stored procedure?
Because an integrity constraint is automatically checked while data is inserted into a table. A stored has to be specifically invoked.
SQL
Home Databases Programming SQL
“SQL Interview Questions and Answers will guide us now that SQL (Structured Query Language) is a database computer language designed for managing data in relational database management systems (RDBMS), and originally based upon Relational Algebra. So learn SQL or get preparation for the job of SQL (Structured Query Language) with the help of this SQL Interview Questions with Answers guide”
172 SQL Questions And Answers
142⟩ What are the advantages of client/server model?
Flexibility of the system, scalability, cost saving, centralised control and implementation of business rules, increase of developers productivity, portability, improved network and resource utilization.
143⟩ What are the types of processes that a server runs?
Foreground process and Background process.
144⟩ What are the different topologies available for network?
Star,
Bus,
Ring.
145⟩ What is an sql integrity constraint?
An integrity constraint allows the definition of certain restrictions, at the table level, on the data that is entered into a table.
146⟩ What is the main advantage of developing an application using an API?
The application can be connected to any back end server that is supported by the API.
147⟩ Which of the Oracle supplied package will enable this feature?
A stored function must return a value based on conditions that are determined at runtime. Therefore, the SELECT statement cannot be hard-coded and must be created dynamically when the function is executed. Which Oracle supplied package will enable this feature?
1. DBMS_DDL
2. DBMS_DML
3. DBMS_SYN
4. DBMS_SQL
148⟩ Give some examples of standard APIs?
Open Database Connectivity (ODBC),
Integrated Database Application Programming Interface (IDAPI),
XOpen
SQL/CLI
149⟩ What are the responsibilities of a Server?
1. Manage resources optimally across multiple clients.
2. Controlling database access and security.
3. Protecting the databse and recovering it from crashes.
4. Enforcing integrity rules globally.
150⟩ What is sql inheritance?
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
151⟩ What is the main disadvantage of developing an application using an API?
The application cannot use any special features of the backend server.
152⟩ What are the four types of events?
1. System Events.
2. Control Events
3. User Events
4. Other Events.
153⟩ What is the function of a ODBC manager?
The ODBC Manager manages all the data sources that exists in the system.
154⟩ What description of a data source is required for ODBC?
The name of the DBMS, the location of the source and the database dependent information.
155⟩ You want to include a carriage return/linefeed in your output from a SQL script, how can you do this?
Expected answer: The best method is to use the CHR() function (CHR(10) is a return/linefeed) and the concatenation function "||". Another method, although it is hard to document and isn?t always portable is to use the return/linefeed as a part of a quoted string.
156⟩ How can you call a PL/SQL procedure from SQL?
Expected answer: By use of the EXECUTE (short form EXEC) command.
157⟩ You want to use SQL to build SQL, what is this called and give an example?
This is called dynamic SQL. An example would be:
set lines 90 pages 0 termout off feedback off verify off
spool drop_all.sql
select ?drop user ?||username||? cascade;? from dba_users
where username not in ("SYS?,?SYSTEM?);
spool off
Essentially you are looking to see that they know to include a command (in this case DROP USER...CASCADE;) and that you need to concatenate using the ?||? the values selected from the database.
158⟩ What is a Cartesian product?
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
159⟩ How do you execute a host operating system command from within SQL?
Expected answer: By use of the exclamation point "!" (in UNIX and some other OS) or the HOST (HO) command.
160⟩ How can variables be passed to a SQL routine?
Level: Low
Expected answer: By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQLPLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself:
"select * from dba_tables where owner=&owner_name;" . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.
