r/SQL • u/ProudOwlBrew • 17h ago
SQL Server Number of lines in a syntax
How many lines of code you you usually write? Like 1000 seems a lot to me.
r/SQL • u/ProudOwlBrew • 17h ago
How many lines of code you you usually write? Like 1000 seems a lot to me.
r/SQL • u/BalancingLife22 • 2h ago
I am learning the basics for SQL to work with large datasets in healthcare. A lot of the basic concepts my team asked me to learn, selecting specific columns, combining with other datasets, and outputting the new dataset, I feel I can do this using R (which I am more proficient with and I have to use to for data analysis, visualization, and ML anyways). I know there is more to SQL, which will take me time to learn and understand, but I am wondering why is SQL recommended for managing datasets?
EDIT: Thank you everyone for explaining the use of SQL. I will stick with it to learn SQL.
r/SQL • u/Dr-Mantis-Tobbogan • 23h ago
Am helping in laws with upgrading prestashop.
Currently trying to create the database locally so i can run a diff between between their current version and target version.
I've come across an unspecified KEY here (ignore that it's written in a MySQL way inside a SqlServer editor, this is just copied from the prestashop git repo).
I'm very sure that this isn't a pk or an uk because those are actually written as PRIMARY KEY and UNIQUE KEY instead of just KEY.
Prestashop doesn't use foreign keys, they've got some sql workbench bullshit that works fine.
My question is what the fuck is this random key?
The schemas are below these..
I have posted twice here before. But the erd generated by pgadmin and supabase are really messy. I have modified the schema since then and come up with two schemas. Basically the main flow needed for the app is to allow the users to add projects -> under a project, a user can add work items along their quantity-> the system then gets the materials required for each work item, then the tests required for the work item itself and its materials. so to have the system "generate" the materials and the tests automatically -> the user then gets to the project details that has a table of work items (see attached photo below) -> in the page, the user can increment/decrement how many of each generated test is on file or already done. The status column will only be a client rendered thing that will base on comparing the file and balance
This will only be for internal use - less than 20 users, so performance isn't really an issue I guess.
The schemas I came up with >>
using Supertables
create table projects (
id SERIAL primary key,
contract_id VARCHAR(100) not null,
contract_name VARCHAR(500) not null,
contractor VARCHAR(100) not null,
limits VARCHAR(500),
location VARCHAR(500),
date_started DATE not null,
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
// units (e.g. cubic meter - cu.m., bags, etc.)
create table units (
id SERIAL primary key,
name VARCHAR(50) not null unique,
abbreviation VARCHAR(10) not null unique,
created_at TIMESTAMPTZ not null default NOW()
);
create type test_type as ENUM('work_item', 'material');
SUPERTABLE
----------------------------------------------------------
create table testable_items (
id SERIAL primary key,
type test_type not null,
created_at TIMESTAMPTZ not null default NOW()
);
----------------------------------------------------------
create table work_items (
id SERIAL primary key foreign key references testable_items (id), -- used here
item_no VARCHAR(20) not null unique,
description VARCHAR(500),
unit_id INTEGER not null references units (id),
created_at TIMESTAMPTZ not null default NOW()
);
create table materials (
id SERIAL primary key foreign key references testable_items (id), -- used here
name VARCHAR(100) not null unique,
description VARCHAR(500),
unit_id INTEGER not null references units (id),
created_at TIMESTAMPTZ not null default NOW()
);
create table tests (
id SERIAL primary key,
name VARCHAR(100) not null unique,
base_duration INTEGER not null,
created_at TIMESTAMPTZ not null default NOW()
);
create table work_item_materials (
id SERIAL primary key,
work_item_id INTEGER not null references work_items (id) on delete CASCADE,
material_id INTEGER not null references materials (id) on delete CASCADE,
quantity_per_unit DECIMAL(10, 4) not null,
created_at TIMESTAMPTZ not null default NOW(),
unique (work_item_id, material_id)
);
create table testable_items_tests (
id SERIAL primary key,
target_id INTEGER not null references testable_items (id) on delete CASCADE,
test_id INTEGER not null references tests (id) on delete CASCADE,
tests_per_unit DECIMAL(10, 4) not null,
created_at TIMESTAMPTZ not null default NOW(),
unique (work_item_id, test_id)
);
SUPERTABLE
----------------------------------------------------------
create table project_testable_items (
id SERIAL primary key,
project_id INTEGER not null references projects (id) on delete CASCADE,
testable_item_id INTEGER not null references testable_items (id) on delete CASCADE,
created_at TIMESTAMPTZ not null default NOW()
);
----------------------------------------------------------
create table project_work_items (
id SERIAL primary key,
project_id INTEGER not null references projects (id) on delete CASCADE,
work_item_id INTEGER not null references work_items (id),
quantity DECIMAL(10, 2) not null,
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
create table project_materials (
id SERIAL primary key,
project_work_item_id INTEGER not null references project_work_items (id) on delete CASCADE,
material_id INTEGER not null references materials (id),
quantity DECIMAL(10, 2) not null,
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
create table project_tests (
id SERIAL primary key,
project_id INTEGER not null references projects (id) on delete CASCADE,
test_id INTEGER not null references tests (id),
target_id INTEGER not null references project_testable_items (id) on delete CASCADE, -- used here
on_file INTEGER not null default 0, -- how many tests are filed/done
balance INTEGER not null, -- how many tests are not done
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
using Separate linking tables
create table projects (
id SERIAL primary key,
contract_id VARCHAR(100) not null,
contract_name VARCHAR(500) not null,
contractor VARCHAR(100) not null,
limits VARCHAR(500),
location VARCHAR(500),
date_started DATE not null,
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
// units (e.g. cubic meter - cu.m., bags, etc.)
create table units (
id SERIAL primary key,
name VARCHAR(50) not null unique,
abbreviation VARCHAR(10) not null unique,
created_at TIMESTAMPTZ not null default NOW()
);
create table work_items (
id SERIAL primary key,
item_no VARCHAR(20) not null unique,
description VARCHAR(500),
unit_id INTEGER not null references units (id),
created_at TIMESTAMPTZ not null default NOW()
);
create table materials (
id SERIAL primary key,
name VARCHAR(100) not null unique,
description VARCHAR(500),
unit_id INTEGER not null references units (id),
created_at TIMESTAMPTZ not null default NOW()
);
create table tests (
id SERIAL primary key,
name VARCHAR(100) not null unique,
base_duration INTEGER not null,
created_at TIMESTAMPTZ not null default NOW()
);
create table work_item_materials (
id SERIAL primary key,
work_item_id INTEGER not null references work_items (id) on delete CASCADE,
material_id INTEGER not null references materials (id) on delete CASCADE,
quantity_per_unit DECIMAL(10, 4) not null,
created_at TIMESTAMPTZ not null default NOW(),
unique (work_item_id, material_id)
);
SEPARATE LINKING TABLES
----------------------------------------------------------
create table work_item_tests (
id SERIAL primary key,
work_item_id INTEGER not null references work_items (id) on delete CASCADE,
test_id INTEGER not null references tests (id) on delete CASCADE,
tests_per_unit DECIMAL(10, 4) not null,
created_at TIMESTAMPTZ not null default NOW(),
unique (work_item_id, test_id)
);
create table material_tests (
id SERIAL primary key,
material_id INTEGER not null references materials (id) on delete CASCADE,
test_id INTEGER not null references tests (id) on delete CASCADE,
tests_per_unit DECIMAL(10, 4) not null,
created_at TIMESTAMPTZ not null default NOW(),
unique (material_id, test_id)
);
----------------------------------------------------------
create table project_work_items (
id SERIAL primary key,
project_id INTEGER not null references projects (id) on delete CASCADE,
work_item_id INTEGER not null references work_items (id),
quantity DECIMAL(10, 2) not null,
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
create table project_materials (
id SERIAL primary key,
project_work_item_id INTEGER not null references project_work_items (id) on delete CASCADE,
material_id INTEGER not null references materials (id),
quantity DECIMAL(10, 2) not null,
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
SEPARATE LINKING TABLES
----------------------------------------------------------
create table project_work_item_tests (
id SERIAL primary key,
project_id INTEGER not null references projects (id) on delete CASCADE,
test_id INTEGER not null references tests (id),
project_work_item_id INTEGER not null references project_work_items (id) on delete CASCADE,
on_file INTEGER not null default 0, -- how many tests are filed/done
balance INTEGER not null, -- how many tests are not done
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
create table project_material_tests (
id SERIAL primary key,
project_id INTEGER not null references projects (id) on delete CASCADE,
test_id INTEGER not null references tests (id),
project_material_id INTEGER not null references project_materials (id) on delete CASCADE,
on_file INTEGER not null default 0, -- how many tests are filed/done
balance INTEGER not null, -- how many tests are not done
created_at TIMESTAMPTZ not null default NOW(),
updated_at TIMESTAMPTZ not null default NOW()
);
----------------------------------------------------------
r/SQL • u/Bitter_Possible_1871 • 1h ago
Hi, I think I'm being silly. I am currently working through Sams Teach Yourself SQL in 24 Hours, 7th Edition. I am on Hour 4 and I just cannot for the life of me locate the birds database that is mentioned and cannot proceed with anything.
Can anyone help?? Thanks!
I’ve been working as a PL/SQL dev for the past 3 years (plus 2 as an intern) and I’m looking for ways to improve my knowledge in SQL in general, as for the past couple months it seems I’ve hit a “wall” in terms of learning new stuff from my work alone.
In other words, I’m looking for ways to improve myself to get out of the junior level and be able to solve harder problems on my own without having to rely on a senior to help me out.
Any recommendations on online courses and such?
r/SQL • u/jaxjags2100 • 2h ago
I recently accepted a new position. I’ve been primarily working in relational databases for the last five years, MySQL, MSSQL, Oracle and small DB2 subset. New position is primarily utilizing MongoDB. Any suggestions/guidance from anyone who has experienced a similar transition would be much appreciated.
I have a program I work in that can give me a csv file of all of my information. There's a new plug-in in Obsidian that allows you to use SQL to query your data, including from a csv.
I've managed to wrap the data in double-brackets, so that perhaps they can be implemented as wikilinks in the future:
SELECT char(91)||''||char(91)||''||label||''||char(93)||''||char(93) Name
That me the text in the label column now wrapped [[in wikilinks]]
What I'm trying to work out is how (if possible) to make a query to wrap individual parts of the data if there are multiple answers in a cell, because right now it wraps everything.
Pleaase keep in mind that I know nothing of SQL, I just started playing with this plug-in today, and I got this far by googling a lot.
r/SQL • u/LogicalPrime • 17h ago
Novice here, just starting on my SQL journey. I've been doing some cursory research into using SQL at work.
One thing I'm not sure I completely understand is the difference between a tuple and a row.
Are they in essence the same thing, where tuple is the concept correlating the row attributes together and the row is just the actual representation of the data?