{"id":17076,"date":"2023-03-23T22:07:00","date_gmt":"2023-03-23T22:07:00","guid":{"rendered":"https:\/\/kiusi.com\/?p=17076"},"modified":"2023-03-23T22:07:01","modified_gmt":"2023-03-23T22:07:01","slug":"conoces-la-herramienta-quick-sql","status":"publish","type":"post","link":"https:\/\/kiusi.com\/en\/conoces-la-herramienta-quick-sql\/","title":{"rendered":"Do you know the Quick SQL tool?"},"content":{"rendered":"

Quick SQL<\/strong> allows us to quickly design and prototype data models using an abbreviated markdown syntax that expands Oracle SQL. We can easily create master\/detail relationships, check constraints and even generate sample data.<\/p>\n\n\n\n

We can use this application for free to generate SQL and use it in any Oracle database.<\/p>\n\n\n\n

We can create prototypes for free using\u00a0Oracle Live SQL<\/a>.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

To use this tool, we need to have a free Oracle.com account.<\/p>\n\n\n\n

Click on the button Start Coding<\/strong>, the window to enter our Oracle credentials appears, and if we do not have credentials we can create a new one.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

In this way we enter the Quick SQL tool (which, by the way, is developed in Oracle APEX \ud83d\ude0a).<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

On the other hand, we can also use this tool if we are subscribed to a free workspace at\u00a0apex.oracle.com<\/a>or if we have a workspace, for example in an autonomous database in the Oracle cloud, we will have access to Quick SQL<\/strong> within APEX.<\/p>\n\n\n\n

From the APEX home page we click on SQL Workshop<\/strong> –> Utilities<\/strong> –> Quick SQL<\/strong>.<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

To learn how to generate the codes, we can load a Quick SQL model, for example, we click on the button\u00a0Load.<\/strong>\u00a0<\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n

Select the data example:\u00a0Departments and Employees<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We load the Sample, and we see how in the left quadrant we have the syntax to generate the SQL statements of what is displayed in the right quadrant.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We see the following code:<\/p>\n\n\n\n

departments \/insert 4\n\u00a0 \u00a0name \/nn\n\u00a0 \u00a0location\n\u00a0 \u00a0country\n\u00a0 \u00a0employees \/insert 14\n\u00a0 \u00a0 \u00a0 name \/nn vc50\n\u00a0 \u00a0 \u00a0 email \/lower\n\u00a0 \u00a0 \u00a0 cost center num\n\u00a0 \u00a0 \u00a0 date hired\n\u00a0 \u00a0 \u00a0 job\nview emp_v departments employees<\/code><\/pre>\n\n\n\n
There are table directives as well as column directives. According to this example we see the following:<\/p>\n\n\n\n
\/insert 4<\/strong> => this means \"Generate 4 SQL INSERT statements with random data\" (table directive)<\/p>\n\n\n\n
\/nn vc50<\/strong> => this means \"Add a non-null constraint to the preceding column, in this case name, further defines that it is a varchar2(50) data type\" (column directive)<\/p>\n\n\n\n
\/lower<\/strong> => this means \"forcing the column to be in lower case\" (column directive)<\/p>\n\n\n\n
num<\/strong> => this means \"of which the column is a numeric data type\".<\/p>\n\n\n\n
Generate the following SQL Script:<\/p>\n\n\n\n
-- create tables\r\ncreate table departments (\r\n    id                             number generated by default on null as identity \r\n                                   constraint departments_id_pk primary key,\r\n    name                           varchar2(255 char) not null,\r\n    location                       varchar2(4000 char),\r\n    country                        varchar2(4000 char)\r\n)\r\n;\r\n\r\ncreate table employees (\r\n    id                             number generated by default on null as identity \r\n                                   constraint employees_id_pk primary key,\r\n    department_id                  number\r\n                                   constraint employees_department_id_fk\r\n                                   references departments on delete cascade,\r\n    name                           varchar2(50 char) not null,\r\n    email                          varchar2(255 char),\r\n    cost_center                    number,\r\n    date_hired                     date,\r\n    job                            varchar2(255 char)\r\n)\r\n;\r\n\r\n-- table index\r\ncreate index employees_i1 on employees (department_id);\r\n\r\n\r\n-- triggers\r\ncreate or replace trigger employees_biu\r\n    before insert or update \r\n    on employees\r\n    for each row\r\nbegin\r\n    :new.email := lower(:new.email);\r\nend employees_biu;\r\n\/\r\n-- create views\r\ncreate or replace view emp_v as \r\nselect \r\n    departments.id                                     department_id,\r\n    departments.name                                   department_name,\r\n    departments.location                               location,\r\n    departments.country                                country,\r\n    employees.id                                       employee_id,\r\n    employees.name                                     employee_name,\r\n    employees.email                                    email,\r\n    employees.cost_center                              cost_center,\r\n    employees.date_hired                               date_hired,\r\n    employees.job                                      job\r\nfrom \r\n    departments,\r\n    employees\r\nwhere\r\n    employees.department_id(+) = departments.id\r\n\/\r\n-- load data\r\ninsert into departments (\r\n    id,\r\n    name,\r\n    location,\r\n    country\r\n) values (\r\n    1,\r\n    'Internal Systems',\r\n    'Tanquecitos',\r\n    'United States'\r\n);\n.........\n.........\n.........<\/code><\/pre>\n\n\n\n
And it continues here by inserting rows in the Department and Employees tables; 4 records in the Departments table and 14 records in the Employees table.<\/p>\n\n\n\n
To learn more about the tool we have the help where it shows us the data types, table and column directives, views, configurations and examples.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
We can save the script, download it and run it directly in our schematic.<\/p>\n\n\n\n
Using this tool makes writing SQL code much easier, I invite you to use it and tell us what you think in the comments. \ud83d\ude0a<\/p>\n\n\n\n
See you soon!<\/p>","protected":false},"excerpt":{"rendered":"
Quick SQL nos permite dise\u00f1ar r\u00e1pidamente y prototipar modelos de datos utilizando una sintaxis abreviada tipo markdown que expande el Oracle SQL. Podemos crear f\u00e1cilmente relaciones maestro\/detalle, comprobar restricciones e incluso generar datos de muestra. Podemos utilizar esta aplicaci\u00f3n en forma gratuita para generar SQL y usarlo en cualquier base de datos Oracle. Podemos crear prototiposContinue reading “\u00bfConoces la herramienta Quick SQL?”<\/span><\/a><\/p>","protected":false},"author":132749711,"featured_media":17092,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"{title}\n\n{excerpt}\n\n{url}","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_wpas_customize_per_network":false,"jetpack_post_was_ever_published":false},"categories":[121913,121890],"tags":[121886,122126,121968],"class_list":["post-17076","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-lenguaje-sql","tag-oracle","tag-quick-sql","tag-sql","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/kiusi.com\/wp-content\/uploads\/2023\/03\/quick-sql1.jpg?fit=2949%2C1735&ssl=1","jetpack_likes_enabled":true,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paMvMS-4rq","amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17076","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/users\/132749711"}],"replies":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/comments?post=17076"}],"version-history":[{"count":8,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17076\/revisions"}],"predecessor-version":[{"id":17093,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/posts\/17076\/revisions\/17093"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/media\/17092"}],"wp:attachment":[{"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/media?parent=17076"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/categories?post=17076"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kiusi.com\/en\/wp-json\/wp\/v2\/tags?post=17076"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}