Database SQL / Scripts

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}

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. 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 9. Count number of records / rows in a table

select count(*) from TABLE_NAME

_{Like Feedback count number of rows count rows count(*) SQL}

Sample 10. 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 11. 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 12. 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 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}

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}

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}

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}

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}

Search Database SQL / Scripts