Tutorial by Examples: sql

The following commands can be used to swap the names of two MySQL databases (<db1> and <db2>): mysqladmin -uroot -p<password> create swaptemp mysqldump -uroot -p<password> --routines <db1> | mysql -uroot -p<password> swaptemp mysqladmin -uroot -p<password&gt...

Renaming a table can be done in a single command: RENAME TABLE `<old name>` TO `<new name>`; The following syntax does exactly the same: ALTER TABLE `<old name>` RENAME TO `<new name>`; If renaming a temporary table, the ALTER TABLE version of the syntax must be used....

Renaming a column can be done in a single statement but as well as the new name, the "column definition" (i.e. its data type and other optional properties such as nullability, auto incrementing etc.) must also be specified. ALTER TABLE `<table name>` CHANGE `<old name>` `<n...

Dynamic SQL enables us to generate and run SQL statements at run time. Dynamic SQL is needed when our SQL statements contains identifier that may change at different compile times. Simple Example of dynamic SQL: CREATE PROC sp_dynamicSQL @table_name NVARCHAR(20), @col_name NVARCHAR(2...

In the .bashrc or .bash_profile, adding: export MYSQL_PS1="\u@\h [\d]>" make the MySQL client PROMPT show current user@host [database].

This sample demonstrates how to import the worksheet Azure Excel file blob to DB on the Azure SQL Server and how to export it from DB to Azure Excel blob. Prerequisites: Microsoft Visual Studio 2015 version Open XML SDK 2.5 for Microsoft Office An Azure storage account Azure SQL Server Add...

SQLite.NET is an open source library which makes it possible to add local-databases support using SQLite version 3 in a Xamarin.Forms project. The steps below demonstrate how to include this component in a Xamarin.Forms Shared Project: Download the latest version of the SQLite.cs class and add...

SELECT val FROM (SELECT val, rownum AS rnum FROM (SELECT val FROM rownum_order_test ORDER BY val) WHERE rownum <= :upper_limit) WHERE rnum >= :lower_limit ; this way we can paginate the table data , just like web serch page

npm install sqlite3 --build-from-source --runtime=node-webkit --target_arch=ia32 --target= target is important. ex:0.16.1 npm rebuild Create a folder for sqlite db. Remember sequalize.sync();

In this example we use a parameter in the route to specify the page number. We set a default of 1 in the function parameter page=1. We have a User object in the database and we query it, ordering in descending order, showing latest users first. We then use the paginate method of the query object in ...

FOR XML PATH and STUFF to concatenate the multiple rows into a single row: select distinct t1.id, STUFF( (SELECT ', ' + convert(varchar(10), t2.date, 120) FROM yourtable t2 where t1.id = t2.id FOR XML PATH ('')) , 1,...

Source Code Blog

Okay it took me about a day to figure it out so here I am posting the steps I followed to get my Database First working in a Class Project (.NET Core), with a .NET Core Web App. Step 1 - Install .NET Core Make Sure you are using .NET Core not DNX (Hint: You should be able to see the .NET Core opti...

This section should list, at least, the out of the box sql functions that come with Teradata Database and also list where there are differences between TD and the ANSI standard.

This UNION ALL combines data from multiple tables and serve as a table name alias to use for your queries: SELECT YEAR(date_time_column), MONTH(date_time_column), MIN(DATE(date_time_column)), MAX(DATE(date_time_column)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio ...

"Data": { "DefaultConnection": { "ConnectionString": "Host=localhost;Username=postgres;Password=******;Database=postgres;Port=5432;Pooling=true;" } },

Homebrew calls itself 'the missing package manager for macOS'. It can be used to build and install applications and libraries. Once installed, you can use the brew command to install PostgreSQL and it's dependencies as follows: brew update brew install postgresql Homebrew generally installs the...

The following are the steps that you will need to follow to setup mysql datasource in JBoss AS 7. Download the MySQL jdbc zip file from here: Create a directory mysql inside /jboss-as-7.1.1.Final/modules/com Inside that create a directory structure like the following: /jboss-as-7.1.1.Final/m...

In some cases, you would need to execute SQL query placed in string. EXEC, EXECUTE, or system procedure sp_executesql can execute any SQL query provided as string: sp_executesql N'SELECT * FROM sys.objects' -- or sp_executesql @stmt = N'SELECT * FROM sys.objects' -- or EXEC sp_executesql N'SEL...

You can execute SQL query as different user using AS USER = 'name of database user' EXEC(N'SELECT * FROM product') AS USER = 'dbo' SQL query will be executed under dbo database user. All permission checks applicable to dbo user will be checked on SQL query.