Share

Search Database SQL / Scripts


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





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

 Sample 1. 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 2. 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 3. Drop a Constraint from a Table

ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME;

   Like      Feedback     drop a constraint  ALTER  DROP CONSTRAINT  DDL


 Sample 4. 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


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. 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


 Sample 6. Count number of records / rows in a table

select count(*) from TABLE_NAME

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


 Sample 7. 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 8. 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 9. 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


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. Oracle - Create a Sequence

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

   Like      Feedback     sequence  create sequence


 Sample 11. Drop a Sequence

DROP SEQUENCE SEQUENCE_NAME;

   Like      Feedback     sequence  drop sequence


 Sample 12. 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


 Sample 13. 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 14. 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


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. 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


 Sample 16. 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 17. 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 18. 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 19. Get next value of the Sequence

SEQUENCE_NAME.NEXTVAL

   Like      Feedback     sequence  sequence.nextval


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. 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 21. Trim Leading and Trailing Square Braces from a Column

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

   Like      Feedback     trim  trim braces


 Sample 22. 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 23. 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 24. 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


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. 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 26. 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 27. 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 28. 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


 Sample 29. 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 30. 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



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



comments powered by Disqus