I am very going through a very basic query and need help from Experts. Please help me. I am using "MySQL" here:

SELECT c.first_name,
       c.last_name,
       c.email
       l.first_name,
       l.last_name,
       l.email
from users u
INNER JOIN contacts c ON u.id = c.user_id
LEFT JOIN leads l ON c.id = l.contact_id
where u.user_id=850
-- and l.contact_id is not NULL

Can I get the first_name, last_name in one column if contact id is not null in lead table.both table has same values

I know I have not provided the good details but can provide if asked... any help will be appreciated.

How do I resolve this problem? I'm trying to import database into a hosting site, but got this message:

From this section in the .sql file:

I recommend storing NULL in the image_count column on rows where it is inapplicable because the type doesn't need it. NULL is different from an integer 0.

InnoDB doesn't store a value in the row for a NULL. So it's pretty low overhead to leave the column NULL on the rows that don't need it. There's a bit stored in the row header for each nullable column, but that will be needed whether you store a value or not.

10k rows is small for an InnoDB table. I wouldn't worry about it unless you need to run this database on a Raspberry Pi or similarly compact platform.

SQL does not have any syntax for SELECT * except-the-columns-I-don't-want. If you use SELECT *, that means all the columns, including those that are NULL. If you want a subset of columns, you have to name all of the ones you want.

That's normal.

The buffer_pool is a "cache". Blocks (16KB each) are loaded as needed from disk. Modified blocks are eventually written back to disk and left in the buffer_pool. A typical application will gradually touch all the blocks; what you see is the buffer_pool growing over time until it hits some limit.

But, there are other things in the buffer_pool, so it would be nice for it to be about 50% bigger than the data. Hence, it will continue growing pass your 33GB of data until somewhere around 50GB.

You should set innodb_buffer_pool_size to about 70% of available RAM. That is, after accounting for other products that run on the same machine.

If you have a lot more data than buffer_pool; it will still work, but with extra I/O.

Crashes I don't see any reason for it crashing.

Do you have any swap space allocated for the OS? If not, then exceeding 100% of RAM would cause a crash. With some swap space, things would get very slow. In either of these cases, low innodb_buffer_pool_size to avoid any swapping. (It's better to allow I/O for caching than for swapping.)

The buffer_pool deliberately leaves about 5% "headroom". When it gets close, it will bump things out of the cache -- not crash.

movies_type() is the CONSTRUCTOR FUNCTION for movies_type

It requires 1 or more instances of movie_type. Each one separated by a comma.

You're only making a single instance in your SQL.

The magic incantation you seek is:

Cast(multiset( select... ) as movies_type)

How It Works

movies_type is a Nested Table type.

You can Construct an NT of cardinality 1 by specifying a single Scalar SQL statement. (this is what you have in your 3rd attempt)

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA'
    ,movies_type(
        (select ref(m) from movie m where movie_id = 7) -- single element
    )
));

Additionally, you can construct an NT of cardinality n by specifying n elements. In this example, I supply 3x Scalar SQL statements.

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA'
    ,movies_type(
        (select ref(m) from movie m where movie_id = 7), -- element 1
        (select ref(m) from movie m where movie_id = 8), -- element 2
        (select ref(m) from movie m where movie_id = 9)  -- element 3
    )
));

But, that's not what you want.

In order to turn a SELECT statement into an Array of elements, you need to use cast(multiset() as data_type) like so:

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA'
    ,cast(multiset(
                    select ref(m) from movie m where movie_id in (7,8,9)
                 ) as movies_type)
));

I am struggling to figure out the best method to set up security on an Azure SQL Managed instance. First, I tried to set up SQL logins, but since MUST_CHANGE isn't supported, I would then have access to everyone's password when I created the login, clearly an unacceptable solution. I then made myself the AD admin, and set up AD accounts. If I CREATE USER FOR EXTERNAL USER in the master database, people can connect, but if I CREATE LOGIN FOR EXTERNAL USER and then perform the CREATE USER commands in the other dbs linking to the login, people still get a login error.

Since I cannot define a DEFAULT_DATABASE on the CREATE LOGIN command, I am really confused about the security context. Am I really supposed to create users for everyone in the master database? That seems like a security hole. Am I missing something here?

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one.

To me, this seems the same as just having a Primary Key, and an extra UNIQUE key.

So I have two questions:

1. if a Primary Key is non-clustered, does it store all the columns with it? Or only the Primary Key columns and the columns referencing the clustered index?

2. I've just read that if the PK isn't the clustered index, then the clustered index does NOT have to be UNIQUE (but it's highly encouraged). Does this mean then that the table could be "randomly sorted" on the rows with the same key?

Yes. For classic failover clusters a disk witness is easier to manage than a file share. Consider the https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum for different witness types.

A fileshare needs another a server to host it, so that's one more server to the enviroment and extra dependency. For extra HA, the share needs to be a clustered SMB file share, which increases complexity. Also, the share needs permissions setup to allow cluster nodes to access it, whilst Windows Clustering does all the work for a quorum disk.

During a parallel query execution (whether or not to go parallel is decided by the cost threshold for parallelism) MAXDOP limits the number of tasks per request.

Assuming cost threshold for parallelism is a low value, there by causing even smaller queries to work in parallel.

When MAXDOP is set to 0, what makes the processors that have done the work wait (resulting in high CXPACKET wait time). Can they not pick up the next work and not just wait?

What makes the processor wait once it has done its job?

From https://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/

This isn’t really a bottleneck per se – the students could go off and do other work – but they like to complain about how they had to wait around for the slow kids.

That complaining is CXPACKET – Class eXchange Packets. The class is turning in their packets, and complaining about the slow kids.`

I'm querying contract data where clients have contracts that get termed and reinstated. Each row on the table has the Client ID, Effective Date, and Term date of each contract so clients can show up multiple times if they have reinstated their contract. I only want to pull clients that show up with a term date and don't have a new row with an active contract.

What I'm doing right now is: selecting all clients with a term date - saving into a spreadsheet selecting all clients showing active - saving into a spreadsheet

v-lookup removing termed clients who also show up in the active spreadsheet. I know there has got to be a better way to do this.