SQL commands

Find which schema you are in useful when you have conenction to a DB, but don't know which schema it can access

SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

List all tables in current schema

SELECT table_name FROM all_tables;

Select a particular object

SELECT *
FROM Vessel
WHERE vessel_code = '7324211';

Select a few objects

SELECT *
FROM Vessel
WHERE vessel_code IN ('7324211', '88888');

Create a table

-- Switch to the database
USE maint;

-- Create the table
CREATE TABLE ag_test (
    name VARCHAR(100) NOT NULL
);

Drop the table

-- Switch to the database
USE maint;

-- Drop the table
DROP TABLE ag_test;

Count all objects of a particular table

SELECT COUNT(*) AS TotalCount
FROM dbo.vessel;

Count records with particular value

SELECT
    COUNT(*) AS record_count
FROM
    [databsename].[dbo].[tablename]
WHERE
    field = 'value';

Delete all records from table

DELETE FROM deal_collateral;

Delete 10000 records and show count

DELETE TOP (10000) FROM dbo.vessel;

SELECT COUNT(*) AS TotalCount
FROM dbo.vessel;

Select something specific

SELECT TOP 5 *
FROM [shipping].[dbo].[vessel_forecasted_value]
WHERE relative_value <> 0 AND ABS(relative_value - 1.0) > 0.0001

Insert into

INSERT INTO vessel_choices_ship_type (ship_type) VALUES ('Multipurpose vessels');
INSERT INTO vessel_choices_ship_sub_type (ship_sub_type, ship_type_id) VALUES ('Containership 1/1.1kTEU Grd (Eco)', (SELECT ship_type FROM vessel_choices_ship_type WHERE ship_type = 'Container'));

INSERT INTO [shipping].[dbo].[vessel_choices_ship_type] ([ship_type])
SELECT DISTINCT [ship_type]
FROM [shipping].[dbo].[vessel_code]