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