DBMS Questions and Answers

1. What is database?                                                                                            A database...

Read more ...

Real time Queries for SQL

Create emp table in your Schema. Insert 10 values, Then try bellow queries. How to retrive the second record in the table(sql)? (select rownum, e.* from emp e WHERE ROWNUM < 3) MINUS (select rownum, e.* from emp e WHERE ROWNUM < 2) ; How to find out the records except first record in the table(sql)? (select rownum, e.* from emp e) MINUS (select rownum,e.* from emp e WHERE ROWNUM < 2) ; or select * from...

Read more ...

Req: How to attach the empno number before predefined value in a particular column in employee table? Using Sequence .....

Create new table, or other wise copy existing table. Then Create Sequence. While Inserting data into database using bellow insert query. Every time empno before added " PREDIFINEDVALUE00 ". ex: PREDIFINEDVALUE001 PREDIFINEDVALUE002 PREDIFINEDVALUE003 ... etc. Queries: CREATE TABLE EMP_ONE AS SELECT * FROM EMP; CREATE SEQUENCE EMP_ONE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY...

Read more ...

Oracle/PLSQL: Synonyms

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. Creating or replacing a synonym The syntax for creating a synonym is: create [or replace]  [public]  synonym [schema .] synonym_name for [schema .] object_name [@ dblink]; The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP...

Read more ...

SQL: VIEWS

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. Creating a VIEW The syntax for creating a VIEW is: CREATE VIEW view_name AS SELECT columns FROM table WHERE predicates; For Example: CREATE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id and...

Read more ...

SQL: Data Types

The following is a list of general SQL datatypes that may not be supported by all relational databases. |-----------+------------+--------------------------------------| | Data Type | Syntax | Explanation (if applicable) | |-----------+------------+--------------------------------------| | integer | integer | | |-----------+------------+--------------------------------------| |...

Read more ...

SQL: COUNT Function

The COUNT function returns the number of rows in a query. The syntax for the COUNT function is: SELECT COUNT(expression) FROM tables WHERE predicates; Note: The COUNT function will only count those records in which the field in the brackets is NOT NULL. For example, if you have the following table called suppliers: |-------------+----------------+------| | Supplier_ID | Supplier_Name | State| |-------------+----------------+------| |...

Read more ...

ROWID and ROWNUM in SQL

ROWNUM and ROWID are both referred to as pseudo-columns. That is, they are not "real" columns that will show up when you DESC a table. They don't actually exist anywhere in the database. But they're available for you to use. In fact, ROWNUM only exists for a row once it is retrieved from a query. It represents the sequential order in which Oracle has retrieved the row. Therefore it will always exist, be at least 1, and...

Read more ...

Oracle/PLSQL: Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. The syntax for a sequence is: CREATE SEQUENCE sequence_name     MINVALUE value     MAXVALUE value     START WITH value     INCREMENT...

Read more ...

Enum: JPA and Enums via @Enumerated

JPA and Enums via @Enumerated share [gp] share [fb] share [tw] share [pin] contribute It can sometimes be desirable to have a Java enum type to represent a particular column in a database. JPA supports converting database data to and from Java enum types via the @javax.persistence.Enumerated annotation. This example will show basic @Enumerated usage in a field...

Read more ...