Search Database SQL / Scripts


  Help us in improving the repository. Add new Queries/Scripts through 'Submit Database SQL / Scripts ' link.





Database SQL / Scripts - 31 Query/Script(s) found

 Sample 1. Get all records that were created in last 10 days

Select * from TABLE_NAME where CREATED_DATE_COLUMN >= SYSDATE - 10

   Like      Feedback     select  SQL  SYSDATE  Date greater than


 Sample 2. Count Number of Departments having employee count greater than 10 ( Counting grouped by records )

SELECT  count(*) FROM EMPLOYEE group by DEPARTMENT having count(*) > 10

   Like      Feedback     Count group by records   group by   groupby


 Sample 3. Oracle - Get all locked Database Objects

SELECT allObj.Owner, allObj.Object_Name, locObj.Oracle_Username, locObj.OS_User_Name  
FROM V$Locked_Object locObj, All_Objects allObj
WHERE locObj.Object_ID = allObj.Object_ID

   Like      Feedback     locked database objects  Locked_Object  All_Objects


 Sample 4. Join Tables

select * from TABLE1 join TABLE2 on COLUMN_TABLE1 = COLUMN_TABLE2 where OTHER_COLUMN_TABLE1 is not null

   Like      Feedback     join tables  sql  query


Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner
 Sample 5. Get count of Employees having salary greater than 1000

Select count(*) from EMPLOYEE e where e.salary > 1000 

   Like      Feedback     count   count of employees having salary greater than   select   query   sql


 Sample 6. Update all records / rows of a Date column to System Date

UPDATE TABLE_NAME SET COLUMN_NAME = SYSDATE;

   Like      Feedback     Update   Update Statement  DML  SysDate


 Sample 7. Cursor to get Ids from one table and then insert records into another ( Get Employee Ids from EMPLOYEE Table and insert records within EMPLOYEE_SALARY with default salary of 1000 )

DECLARE
   cursor employeeIds IS
      select * from EMPLOYEE;

   BEGIN
      FOR rec IN employeeIds LOOP
         INSERT INTO EMPLOYEE_SALARY(ID, SALARY) VALUES(rec.ID, 1000);
      END LOOP;
END;

   Like      Feedback     cursor


 Sample 8. Oracle - Add New Date Column to a Table

ALTER TABLE TABLE_NAME
ADD NEW_COLUMN DATE;

   Like      Feedback     DDL  alter table  add a column   add a new column  sql


 Sample 9. Add New Date Column with Comment

ALTER TABLE_NAME
ADD COLUMN_NAME DATE;
COMMENT ON COLUMN TABLE_NAME.COLUMN_NAME IS 'Date Column for xyz';

   Like      Feedback     Add Column with comment  Add a Column with comment  Add Column  Alter  Comment  COMMENT ON COLUMN


Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner
 Sample 10. Count number of records / rows in a table

select count(*) from TABLE_NAME

   Like      Feedback     count number of rows  count rows  count(*)  SQL


 Sample 11. Oracle - Create Table with Primary Key, Not Null and Foreign Key Constraints

CREATE TABLE TABLE_NAME (
ID NUMBER CONSTRAINT ID_KEY PRIMARY KEY,
NAME VARCHAR(50) CONSTRAINT NAME_NOT_NULL NOT NULL,
CONSTRAINT NAME_FK FOREIGN KEY (NAME) REFERENCES OTHER_TABLE_NAME (NAME)
);

   Like      Feedback     create table  create table with foreign key


 Sample 12. Get all records / rows having null for a particular column

select * from TABLE_NAME where COLUMN_NAME is null

   Like      Feedback     check null   check null for column  is null


 Sample 13. Drop a Constraint from a Table

ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME;

   Like      Feedback     drop a constraint  ALTER  DROP CONSTRAINT  DDL


 Sample 14. Oracle - Create a Sequence

CREATE SEQUENCE NEW_SEQUENCE start with 1 increment by 1 cache 100;

   Like      Feedback     sequence  create sequence


Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner
 Sample 15. Get next value of the Sequence

SEQUENCE_NAME.NEXTVAL

   Like      Feedback     sequence  sequence.nextval


 Sample 16. Trim Leading and Trailing Square Braces from a Column

SELECT TRIM(']' from TRIM('[' from HIERARCHY)) from CLASS_INFO

   Like      Feedback     trim  trim braces


 Sample 17. Add Primary Key constraint to a Table

ALTER TABLE TABLE_NAME
ADD CONSTRAINT CONSTRAINT_NAME primary key (COLUMN_NAME);

   Like      Feedback     Add Primary Key Constraint  Constraint  Alter Table  Alter  ADD CONSTRAINT   DDL


 Sample 18. Drop a Sequence

DROP SEQUENCE SEQUENCE_NAME;

   Like      Feedback     sequence  drop sequence


 Sample 19. Check if the record / row is not null for a particular column

select * from TABLE_NAME where COLUMN_NAME is not null

   Like      Feedback     check null   check null for column  is not null


Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner
 Sample 20. SQL With Clause

with employee_dept_marketing as (

select * from EMPLOYEE

join DEPT on EMP_DEPT_ID = DEPT_ID

where DEPT_NAME = 'Marketing'

)

select PROFILE_NAME from PROFILE p

join employee_dept_marketing on PROFILE_ID = p.ID

where p.REQUIRED_EXPERIENCE > 10;

   Like      Feedback     sql with clause  with as


 Sample 21. Get all records having date since a particular date

select * from EMPLOYEE where JOINING_DATE between to_date('01-MAY-14', 'DD-MON-YY') and SYSDATE

   Like      Feedback     select  query  to_date  sysdate  get records between 2 dates  get records for last 2 years


 Sample 22. Create Unique Index on Multiple Columns

CREATE UNIQUE INDEX INDEX_EMPLOYEE_ID_DEPT on EMPLOYEE(ID,DEPT);

   Like      Feedback     create index on multiple columns


 Sample 23. Display pairs of Department Names where first dept name has lesser employees than later

Select D1.name , D2.name from Dept D1, Dept D2 where D1.employee_count < D2.employee_count 

   Like      Feedback     Display pairs of Department Names where first dept name has lesser employees than later   self join  select statement   example of self join


 Sample 24. Check the fields of a table - EMPLOYEE

desc EMPLOYEE

   Like      Feedback     desc


Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner
 Sample 25. Update a column (COLUMN1) value to 'abc' for all records having the value of 'xyz' in another column (COLUMN2)

update TABLE_NAME set COLUMN1 = 'abc' where COLUMN2 = 'xyz'

   Like      Feedback     update table  update column  DML


 Sample 26. Replace a String in a particular column of Table

update EMPLOYEE set PROFILE_URL = replace(PROFILE_URL,'http:/xyz.com','http:/abc.com') where DEPT='Marketing'

   Like      Feedback     replace string  dml  update column  replace


 Sample 27. Set Primary Key on an already created table

ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMPLOYEE#ID_PRIMARY_KEY
PRIMARY KEY (ID);

   Like      Feedback     set primary key after table creation   add primary key constraint


 Sample 28. Get all Departments having no employees, Usage of not in

Select DEPT_NAME from DEPT where DEPT_ID not in ( Select distinct DEPT_ID from EMPLOYEE ) 

   Like      Feedback     Not in   Select


 Sample 29. Delete last n records from Oracle Database table

DELETE FROM <TABLE_NAME> WHERE rownum > 100

   Like      Feedback     


Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner
 Sample 30. Adding Composite Key as Primary Key

ALTER TABLE TABLE_NAME
ADD CONSTRAINT CONSTRAINT_NAME primary key (COLUMN_1_NAME,COLUMN_2_NAME );

   Like      Feedback     composite primary key   primary key  alter  alter table  constraint  add constraint


 Sample 31. Select Employee names having salary between 2000 to 3000 USD

Select EMPLOYEE_NAME from EMPLOYEE where SALARY between 2000 and 3000

   Like      Feedback     between  select



Subscribe to Java News and Posts. Get latest updates and posts on Java from Buggybread.com
Enter your email address:
Delivered by FeedBurner